—— AlmaLinux 9 + TLS 单向认证 + 账号密码认证版（适用于生产环境，2025 最新实践）只要求服务器 TLS 证书、客户端仅需用户名密码登录集群拓扑节点角色主机名IP 地址操作系统PRIMARYmongo1192.168.6.111AlmaLinux 9SECONDARYmongo2192.168.6.112AlmaLinux 9SECONDARYmongo3192.168.6.113AlmaLinux 9副本集名称：test1MongoDB 版本：8.2.x认证方式：用户名 + 密码（SCRAM-SHA-256）加密通信：TLS 单向认证（仅服务端证书）🧩 阶段 0：基础环境准备（所有节点执行）1️⃣ 添加 MongoDB Yum 源sudo tee /etc/yum.repos.d/mongodb-org-8.2.repo <<EOF [mongodb-org-8.2] name=MongoDB Repository baseurl=https://repo.mongodb.org/yum/redhat/9/mongodb-org/8.2/x86_64/ gpgcheck=1 enabled=1 gpgkey=https://pgp.mongodb.com/server-8.0.asc EOF2️⃣ 安装 MongoDBsudo dnf remove -y mongodb-org* # 如有旧版本先卸载 sudo dnf clean all sudo dnf install -y mongodb-org3️⃣ 配置主机名解析编辑 /etc/hosts：192.168.6.111 mongo1 192.168.6.112 mongo2 192.168.6.113 mongo34️⃣ 创建证书存放目录sudo mkdir -p /etc/mongodb/pki sudo chown -R mongod:mongod /etc/mongodb/pki sudo chmod 700 /etc/mongodb/pki5️⃣ 防火墙与 SELinux 设置sudo firewall-cmd --zone=public --add-port=27017/tcp --permanent sudo firewall-cmd --reload sudo setenforce 0 sudo sed -i 's/^SELINUX=enforcing$/SELINUX=permissive/' /etc/selinux/config🧩 阶段 1：生成与分发安全凭证（仅在 mongo1 执行）1️⃣ 生成内部认证 KeyFilesudo openssl rand -base64 756 > /etc/mongodb/pki/mongodb.key sudo chmod 400 /etc/mongodb/pki/mongodb.key sudo chown mongod:mongod /etc/mongodb/pki/mongodb.key2️⃣ 创建 TLS 服务端证书（CA + Server）cd /etc/mongodb/pki # 创建 CA 根证书 openssl genrsa -out ca.key 4096 openssl req -x509 -new -key ca.key -days 3650 -out root.pem -subj "/C=CN/ST=Beijing/L=Beijing/O=TianhaoTech/CN=MongoCA" # 创建服务器证书 openssl genrsa -out server.key 4096 openssl req -new -key server.key -out server.csr -subj "/C=CN/ST=Beijing/L=Beijing/O=TianhaoTech/CN=mongodb-cluster" openssl x509 -req -in server.csr -CA root.pem -CAkey ca.key -CAcreateserial -out server.crt -days 3650 -extensions v3_req \ -extfile <(printf "[v3_req]\nsubjectAltName=DNS:mongo1,DNS:mongo2,DNS:mongo3,IP:192.168.6.111,IP:192.168.6.112,IP:192.168.6.113") # 合并为 PEM cat server.key server.crt > server.pem # 设置权限 sudo chown mongod:mongod /etc/mongodb/pki/* sudo chmod 600 /etc/mongodb/pki/*.pem sudo chmod 400 /etc/mongodb/pki/ca.key3️⃣ 分发到其他节点scp /etc/mongodb/pki/{mongodb.key,root.pem,server.pem} root@mongo2:/etc/mongodb/pki/ scp /etc/mongodb/pki/{mongodb.key,root.pem,server.pem} root@mongo3:/etc/mongodb/pki/ ssh root@mongo2 "chown -R mongod:mongod /etc/mongodb/pki && chmod 700 /etc/mongodb/pki" ssh root@mongo3 "chown -R mongod:mongod /etc/mongodb/pki && chmod 700 /etc/mongodb/pki"🧩 阶段 2：配置与初始化副本集1️⃣ 所有节点配置/etc/mongod.confsystemLog: destination: file logAppend: true path: /var/log/mongodb/mongod.log storage: dbPath: /var/lib/mongo journal: enabled: true net: port: 27017 bindIp: 0.0.0.0 tls: mode: requireTLS certificateKeyFile: /etc/mongodb/pki/server.pem CAFile: /etc/mongodb/pki/root.pem allowConnectionsWithoutCertificates: true replication: replSetName: "test1" security: authorization: "enabled" keyFile: /etc/mongodb/pki/mongodb.key2️⃣ 启动并设置开机自启sudo systemctl enable mongod --now3️⃣ 初始化副本集（仅在 mongo1 上执行）mongosh --host mongo1 --tls \ --tlsCAFile /etc/mongodb/pki/root.pem \ --tlsCertificateKeyFile /etc/mongodb/pki/server.pem在 mongosh 中执行：rs.initiate({ _id: "test1", members: [ { _id: 0, host: "mongo1:27017" }, { _id: 1, host: "mongo2:27017" }, { _id: 2, host: "mongo3:27017" } ] })等待状态为 PRIMARY：rs.status()🧩 阶段 3：创建管理员用户在 mongo1 (PRIMARY) 节点中执行：use admin db.createUser({ user: "admin", pwd: passwordPrompt(), roles: [ { role: "root", db: "admin" } ] }) exit🧩 阶段 4：验证集群连接1️⃣ 管理员验证mongosh "mongodb://admin@mongo1:27017,mongo2:27017,mongo3:27017/admin?replicaSet=test1" \ --tls --tlsCAFile /etc/mongodb/pki/root.pem输入密码后执行：rs.status()应看到 PRIMARY + 2 个 SECONDARY。🧩 阶段 5：客户端连接方式（单向 TLS）✅ 客户端仅需：root.pem（CA 根证书）用户名 + 密码无需客户端证书（因为 allowConnectionsWithoutCertificates: true 已启用）1️⃣ mongosh 命令示例mongosh "mongodb://admin@mongo1:27017,mongo2:27017,mongo3:27017/admin?replicaSet=test1" \ --tls --tlsCAFile /path/to/root.pem2️⃣ Java 连接示例String uri = "mongodb://admin:Hmsk@88..@mongo1:27017,mongo2:27017,mongo3:27017/admin?replicaSet=test1&tls=true&tlsCAFile=/path/to/root.pem"; MongoClient mongoClient = MongoClients.create(uri);✅ 验证通过后，你的最终安全体系是：模块认证机制说明副本集内部通信keyFile三节点自动同步认证管理用户登录SCRAM-SHA-256用户名密码通信加密TLS 单向认证客户端验证服务器证书客户端证书❌ 不需要因为已启用 allowConnectionsWithoutCertificates: true
HDFS介绍我们前面已经知道了HDFS是一个分布式的文件系统，具体这个分布式文件系统是如何实现的呢？我们来举个例子分析一下。小明租房：小明同学呢，今年是大四，需要离校去实习了，出去实习首要解决的是住宿的问题，目前软件行业很少包住宿的，反正我是没听说过，但是小明呢，有一个特点，就是懒，还穷，天天出去住酒店肯定是住不起的，那只能租房了，租房呢他还不想自己去找房子，嫌麻烦，所以就随便在路边找了一个二房东贴的广告，单间出租，拎包入住，无中介费，押一付一，一月300，小明一看，可以，还不错，所以就直接打电话过去看房子办手续了，这样就解决了住宿的问题。紧接着小明的同学看他这么快就搞定了住宿问题，房租也挺便宜，都问他要二房东的电话，小明就把二房东的电话发到了他们的年级群里，上千人啊，结果二房东的手机就被打爆了，最后只能关机了，人太多了，实在搞不定了。因为目前有租房需求的学员太多了，全部都去找这一个二房东，他是搞不定的，因为他每天带看的人数是有限的。那这个时候怎么办呢？小明同学还是比较热心的，发现二房东解决不了这么大的需求，然后他正发愁呢，结果一抬头看到了一个中介公司，小明露出了满嘴的大白牙。然后他就顺利搞到了这个中介公司的电话，赶紧把这个电话发到了年级群里面，这个时候，当再有学员有租房需求的时候，直接打中介公司的电话，然后中介公司会让具体的工作人员直接联系这个学员，负责他的租房需求，每一个工作人员最多负责5个人，因为中介公司里面是有很多工作人员的，这样一分流，就很轻松的解决了这个问题。这个时候针对中介公司而言，它只负责管理房源和工作人员就行了，具体干活的是工作人员。这样就算同时过来很多人，中介公司也是可以扛得住的，因为具体看房租房的流程是直接和工作人员对接的，不会造成阻塞。ok 这就是小明租房的一个案例，那这个案例和我们要讲的HDFS有什么关系呢？不要着急，我们来往下面看。假设让我们来设计一个分布式的文件系统，我们该如何设计呢？看这个图片，用户在写入数据和读取数据的时候会通过一个文件系统，这个文件系统后面会有很多台服务器，所以我们的数据就可以存储在多台服务器上面。后期服务器增加或者减少，对我们用户而言，不用关注，这个统一由文件系统进行管理，我们只需要和文件系统进行交互就可以了。这样是不是就实现了分布式存储了，这种方案在实际应用中可行吗？在这里就可以把文件系统理解为二房东，服务器理解为房子，这种设计架构会存在一个问题，假设同时过来很多人都需要租房子，那么一个二房东是忙不过来的，就会造成阻塞。那继续往下面看[]()现在这种设计是，我们去找一个中介公司，这里的主节点就可以理解为一个中介公司这里的从节点就可以理解为是房源，中介公司会在每块房源都安排一个工作人员，当我们找房子的时候，先联系中介公司，中介公司会告诉我们哪里有房子，并且把对应工作人员的信息告诉我们，我们就可以直接去找对应的工作人员去租房子。这样对于中介公司而言，就没什么压力了。中介公司只负责管理房源和工作人员信息，具体干活的是工作人员。这样就算同时过来很多人，中介公司也是可以扛得住的，因为具体看房租房的流程是我们直接和工作人员联系的，不会造成阻塞。所以对应到我们的分布式存储设计上面：用户请求查看数据时候会请求主节点，主节点上面会维护所有数据的存储信息，主节点会把对应数据所在的节点信息返回给用户，然后用户根据数据所在的节点信息去对应的节点去读取数据，这样压力就不会全部在主节点上面。这个就是HDFS这个分布式文件系统的设计思想。HDFS(Hadoop Distributed File System)HDFS的全称是Hadoop Distributed File System ，Hadoop的 分布式 文件 系统它是一种允许文件通过网络在多台主机上分享的文件系统，可以让多台机器上的多个用户分享文件和存储空间其实分布式文件管理系统有很多，HDFS只是其中一种实现而已还有 GFS(谷歌的)、TFS(淘宝的)、S3(亚马逊的)为什么会有多种分布式文件系统呢？这样不是重复造轮子吗？不是的，因为不同的分布式文件系统的特点是不一样的，HDFS是一种适合大文件存储的分布式文件系统，不适合小文件存储，什么叫小文件，例如，几KB，几M的文件都可以认为是小文件HDFS的Shell介绍通过前面的学习，我们对HDFS有了基本的了解，下面我们就想实际操作一下，来通过实操加深对HDFS的理解针对HDFS，我们可以在shell命令行下进行操作，就类似于我们操作linux中的文件系统一样，但是具体命令的操作格式是有一些区别的格式如下：使用hadoop bin目录的hdfs命令，后面指定dfs，表示是操作分布式文件系统的，这些属于固定格式。如果在PATH中配置了hadoop的bin目录，那么这里可以直接使用hdfs就可以了这里的xxx是一个占位符，具体我们想对hdfs做什么操作，就可以在这里指定对应的命令了大多数hdfs 的命令和对应的Linux命令类似HDFS的schema是hdfs，authority是集群中namenode所在节点的ip和对应的端口号，把ip换成主机名也是一样的，path是我们要操作的文件路径信息其实后面这一长串内容就是core-site.xml配置文件中fs.defaultFS属性的值，这个代表的是HDFS的地址。HDFS的常见Shell操作下面我们就来学习一下HDFS中的一些常见的shell操作其实hdfs后面支持很多的参数，但是有很多是很少用的，在这里我们把一些常用的带着大家一块学习一下，如果大家后期有一些特殊的需求，可以试着来看一下hdfs的帮助文档直接在命令行中输入hdfs dfs，可以查看dfs后面可以跟的所有参数注意：这里面的[]表示是可选项，<>表示是必填项[root@bigdata01 hadoop-3.2.0]# hdfs dfs Usage: hadoop fs [generic options] [-appendToFile <localsrc> ... <dst>] [-cat [-ignoreCrc] <src> ...] [-checksum <src> ...] [-chgrp [-R] GROUP PATH...] [-chmod [-R] <MODE[,MODE]... | OCTALMODE> PATH...] [-chown [-R] [OWNER][:[GROUP]] PATH...] [-copyFromLocal [-f] [-p] [-l] [-d] [-t <thread count>] <localsrc> ... <dst>] [-copyToLocal [-f] [-p] [-ignoreCrc] [-crc] <src> ... <localdst>] [-count [-q] [-h] [-v] [-t [<storage type>]] [-u] [-x] [-e] <path> ...] [-cp [-f] [-p | -p[topax]] [-d] <src> ... <dst>] [-createSnapshot <snapshotDir> [<snapshotName>]] [-deleteSnapshot <snapshotDir> <snapshotName>] [-df [-h] [<path> ...]] [-du [-s] [-h] [-v] [-x] <path> ...] [-expunge] [-find <path> ... <expression> ...] [-get [-f] [-p] [-ignoreCrc] [-crc] <src> ... <localdst>] [-getfacl [-R] <path>] [-getfattr [-R] {-n name | -d} [-e en] <path>] [-getmerge [-nl] [-skip-empty-file] <src> <localdst>] [-head <file>] [-help [cmd ...]] [-ls [-C] [-d] [-h] [-q] [-R] [-t] [-S] [-r] [-u] [-e] [<path> ...]] [-mkdir [-p] <path> ...] [-moveFromLocal <localsrc> ... <dst>] [-moveToLocal <src> <localdst>] [-mv <src> ... <dst>] [-put [-f] [-p] [-l] [-d] <localsrc> ... <dst>] [-renameSnapshot <snapshotDir> <oldName> <newName>] [-rm [-f] [-r|-R] [-skipTrash] [-safely] <src> ...] [-rmdir [--ignore-fail-on-non-empty] ...] [-setfacl [-R] [{-b|-k} {-m|-x <acl_spec>} <path>]|[--set <acl_spec> <path>]] [-setfattr {-n name [-v value] | -x name} <path>] [-setrep [-R] [-w] <rep> <path> ...] [-stat [format] <path> ...] [-tail [-f] <file>] [-test -[defsz] <path>] [-text [-ignoreCrc] <src> ...] [-touch [-a] [-m] [-t TIMESTAMP ] [-c] <path> ...] [-touchz <path> ...] [-truncate [-w] <length> <path> ...] [-usage [cmd ...]] Generic options supported are: -conf <configuration file> specify an application configuration file -D <property=value> define a value for a given property -fs <file:///|hdfs://namenode:port> specify default filesystem URL to use, overrides 'fs.defaultFS' property from configurations. -jt <local|resourcemanager:port> specify a ResourceManager -files <file1,...> specify a comma-separated list of files to be copied to the map reduce cluster -libjars <jar1,...> specify a comma-separated list of jar files to be included in the classpath -archives <archive1,...> specify a comma-separated list of archives to be unarchived on the compute machines The general command line syntax is: command [genericOptions] [commandOptions]-ls：查询指定路径信息首先看第一个ls命令查看hdfs根目录下的内容，什么都不显示，因为默认情况下hdfs中什么都没有[root@bigdata01 hadoop-3.2.0]# hdfs dfs -ls hdfs://bigdata01:9000/ [root@bigdata01 hadoop-3.2.0]#其实后面hdfs的url这一串内容在使用时默认是可以省略的，因为hdfs在执行的时候会根据HDOOP_HOME自动识别配置文件中的fs.defaultFS属性所以这样简写也是可以的[root@bigdata01 hadoop-3.2.0]# hdfs dfs -ls / [root@bigdata01 hadoop-3.2.0]#-put：从本地上传文件接下来我们向hdfs中上传一个文件，使用Hadoop中的README.txt，直接上传到hdfs的根目录即可[root@bigdata01 hadoop-3.2.0]# hdfs dfs -put README.txt /上传成功之后没有任何提示，注意，没有提示就是最好的结果确认一下刚才上传的文件[root@bigdata01 hadoop-3.2.0]# hdfs dfs -ls / Found 1 items -rw-r--r-- 2 root supergroup 1361 2020-04-08 15:34 /README.txt在这里可以发现使用hdfs中的ls查询出来的信息和在linux中执行ll查询出来的信息是类似的在这里能看到这个文件就说明刚才的上传操作是成功的-cat：查看HDFS文件内容文件上传上去以后，我们还想查看一下HDFS中文件的内容，很简单，使用cat即可[root@bigdata01 hadoop-3.2.0]# hdfs dfs -cat /README.txt For the latest information about Hadoop, please visit our website at: http://hadoop.apache.org/ and our wiki, at: http://wiki.apache.org/hadoop/ ...........-get：下载文件到本地如果我们想把hdfs中的文件下载到本地linux文件系统中需要怎么做呢？使用get即可实现[root@bigdata01 hadoop-3.2.0]# hdfs dfs -get /README.txt . get: `README.txt': File exists注意：这样执行报错了，提示文件已存在，我这条命令的意思是要把HDFS中的README.txt下载当前目录中，但是当前目录中已经有这个文件了，要么换到其它目录，要么给文件重命名[root@bigdata01 hadoop-3.2.0]# hdfs dfs -get /README.txt README.txt.bak [root@bigdata01 hadoop-3.2.0]# ll total 188 drwxr-xr-x. 2 1001 1002 203 Jan 8 2019 bin drwxr-xr-x. 3 1001 1002 20 Jan 8 2019 etc drwxr-xr-x. 2 1001 1002 106 Jan 8 2019 include drwxr-xr-x. 3 1001 1002 20 Jan 8 2019 lib drwxr-xr-x. 4 1001 1002 4096 Jan 8 2019 libexec -rw-rw-r--. 1 1001 1002 150569 Oct 19 2018 LICENSE.txt -rw-rw-r--. 1 1001 1002 22125 Oct 19 2018 NOTICE.txt -rw-rw-r--. 1 1001 1002 1361 Oct 19 2018 README.txt -rw-r--r--. 1 root root 1361 Apr 8 15:41 README.txt.bak drwxr-xr-x. 3 1001 1002 4096 Apr 7 22:08 sbin drwxr-xr-x. 4 1001 1002 31 Jan 8 2019 share-mkdir [-p]：创建文件夹后期我们需要在hdfs中维护很多文件，所以就需要创建文件夹来进行分类管理了下面我们来创建一个文件夹，hdfs中使用mkdir命令[root@bigdata01 hadoop-3.2.0]# hdfs dfs -mkdir /test [root@bigdata01 hadoop-3.2.0]# hdfs dfs -ls / Found 2 items -rw-r--r-- 2 root supergroup 1361 2020-04-08 15:34 /README.txt drwxr-xr-x - root supergroup 0 2020-04-08 15:43 /test如果要递归创建多级目录，还需要再指定-p参数[root@bigdata01 hadoop-3.2.0]# hdfs dfs -mkdir -p /abc/xyz You have new mail in /var/spool/mail/root [root@bigdata01 hadoop-3.2.0]# hdfs dfs -ls / Found 3 items -rw-r--r-- 2 root supergroup 1361 2020-04-08 15:34 /README.txt drwxr-xr-x - root supergroup 0 2020-04-08 15:44 /abc drwxr-xr-x - root supergroup 0 2020-04-08 15:43 /test想要递归显示所有目录的信息，可以在ls后面添加-R参数[root@bigdata01 hadoop-3.2.0]# hdfs dfs -ls -R / -rw-r--r-- 2 root supergroup 1361 2020-04-08 15:34 /README.txt drwxr-xr-x - root supergroup 0 2020-04-08 15:44 /abc drwxr-xr-x - root supergroup 0 2020-04-08 15:44 /abc/xyz drwxr-xr-x - root supergroup 0 2020-04-08 15:43 /test-rm [-r]：删除文件/文件夹如果想要删除hdfs中的目录或者文件，可以使用rm删除文件[root@bigdata01 hadoop-3.2.0]# hdfs dfs -rm /README.txt Deleted /README.txt删除目录，注意，删除目录需要指定-r参数[root@bigdata01 hadoop-3.2.0]# hdfs dfs -rm /test rm: `/test': Is a directory [root@bigdata01 hadoop-3.2.0]# hdfs dfs -rm -r /test Deleted /test如果是多级目录，可以递归删除吗？可以[root@bigdata01 hadoop-3.2.0]# hdfs dfs -rm -r /abc Deleted /abcHDFS案例实操需求：统计HDFS中文件的个数和每个文件的大小我们先向HDFS中上传几个文件，把hadoop目录中的几个txt文件上传上去[root@bigdata01 hadoop-3.2.0]# hdfs dfs -put LICENSE.txt / [root@bigdata01 hadoop-3.2.0]# hdfs dfs -put NOTICE.txt / [root@bigdata01 hadoop-3.2.0]# hdfs dfs -put README.txt /1：统计根目录下文件的个数[root@bigdata01 hadoop-3.2.0]# hdfs dfs -ls / |grep /| wc -l 32：统计根目录下每个文件的大小，最终把文件名称和大小打印出来[root@bigdata01 hadoop-3.2.0]# hdfs dfs -ls / |grep / | awk '{print $8,$5}' /LICENSE.txt 150569 /NOTICE.txt 22125 /README.txt 1361Java代码操作HDFS前面我们学习了在shell命令行下操作hdfs，shell中操作hdfs是比较常见的操作，但是在工作中也会遇到一些需求是需要通过代码操作hdfs的，下面我们就来看一下如何使用java代码操作hdfs在具体操作之前需要先明确一下开发环境，代码编辑器使用idea，当然了eclipse也可以在创建项目的时候我们会创建maven项目，使用maven来管理依赖，是比较方便的。在这里我会把idea的安装包和maven的安装包发给大家考虑可能会有同学没有使用过maven，所以在这里大致说一下maven在windows中的安装配置。在这里我们使用apache-maven-3.0.5-bin.zip ，当然了，其它版本也可以，没有什么本质的区别把apache-maven-3.0.5-bin.zip解压到某一个目录下面，在这里我解压到了D:\Program Files (x86)\apache-maven-3.0.5目录解压之后，建议修改一下maven的配置文件，把maven仓库的地址修改到其它盘，例如D盘，默认是在C盘的用户目录下修改D:\Program Files (x86)\apache-maven-3.0.5\conf下的settings.xml文件将localRepository标签从注释中移出来，然后将值该为D:.m2，效果如下：这里的目录名字可以随意起，只要易于识别就可以<localRepository>D:\.m2</localRepository>这样修改之后，maven管理的依赖jar包都会保存到D:.m2目录下了。接下来需要配置maven的环境变量，和windows中配置JAVA_HOME环境变量是一样的。先在环境变量中配置M2_HOME=D:\Program Files (x86)\apache-maven-3.0.5然后在PATH环境变量中添加%M2_HOME%\bin即可环境变量配置完毕以后，打开cmd窗口，输入mvn命令，只要能正常执行就说明windows本地的maven环境配置好了。这还没完，还需要在idea中指定我们本地的maven配置点击idea左上角的File-->Settings，进入如下界面，搜索maven，把本地的maven添加到这里面即可。下面我们来创建一个maven项目项目名称为db_hadoop注意：项目创建好以后，在新打开的界面中需要点击右小角的Enable Auto Import，这样添加到maven依赖会自动引入，否则会发现引入依赖了，但是代码中还是识别不了，这个时候还需要手动引入，比较麻烦。ok，项目创建好了以后，就需要引入hadoop的依赖了在这里我们需要引入hadoop-client依赖包，到maven仓库中去找，添加到pom.xml文件中<dependency> <groupId>org.apache.hadoop</groupId> <artifactId>hadoop-client</artifactId> <version>3.2.0</version> </dependency>然后创建代码上传文件package com.imooc.hdfs; import org.apache.hadoop.conf.Configuration; import org.apache.hadoop.fs.FSDataOutputStream; import org.apache.hadoop.fs.FileSystem; import org.apache.hadoop.fs.Path; import org.apache.hadoop.io.IOUtils; import java.io.FileInputStream; import java.net.URI; /** * Java代码操作HDFS * 文件操作：上传文件、下载文件、删除文件 * Created by xuwei */ public class HdfsOp { public static void main(String[] args) throws Exception{ //创建一个配置对象 Configuration conf = new Configuration(); //指定HDFS的地址 conf.set("fs.defaultFS","hdfs://bigdata01:9000"); //获取操作HDFS的对象 FileSystem fileSystem = FileSystem.get(conf); //获取HDFS文件系统的输出流 FSDataOutputStream fos = fileSystem.create(new Path("/user.txt")); //获取本地文件的输入流 FileInputStream fis = new FileInputStream("D:\\user.txt"); //上传文件：通过工具类把输入流拷贝到输出流里面，实现本地文件上传到HDFS IOUtils.copyBytes(fis,fos,1024,true); } }执行代码，发现报错，提示权限拒绝，说明windows中的这个用户没有权限向HDFS中写入数据Caused by: org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.AccessControlException): Permission denied: user=yehua, access=WRITE, inode="/":root:supergroup:drwxr-xr-x解决办法有两个第一种：去掉hdfs的用户权限检验机制，通过在hdfs-site.xml中配置dfs.permissions.enabled为false即可第二种：把代码打包到linux中执行在这里为了在本地测试方便，我们先使用第一种方式1：停止Hadoop集群[root@bigdata01 ~]# cd /data/soft/hadoop-3.2.0 [root@bigdata01 hadoop-3.2.0]# sbin/stop-all.sh Stopping namenodes on [bigdata01] Last login: Wed Apr 8 20:25:17 CST 2020 from 192.168.182.1 on pts/1 Stopping datanodes Last login: Wed Apr 8 20:25:40 CST 2020 on pts/1 Stopping secondary namenodes [bigdata01] Last login: Wed Apr 8 20:25:41 CST 2020 on pts/1 Stopping nodemanagers Last login: Wed Apr 8 20:25:44 CST 2020 on pts/1 Stopping resourcemanager Last login: Wed Apr 8 20:25:47 CST 2020 on pts/12：修改hdfs-site.xml配置文件注意：集群内所有节点中的配置文件都需要修改，先在bigdata01节点上修改，然后再同步到另外两个节点上在bigdata01上操作[root@bigdata01 hadoop-3.2.0]# vi etc/hadoop/hdfs-site.xml <configuration> <property> <name>dfs.replication</name> <value>2</value> </property> <property> <name>dfs.namenode.secondary.http-address</name> <value>bigdata01:50090</value> </property> <property> <name>dfs.permissions.enabled</name> <value>false</value> </property> </configuration>同步到另外两个节点中[root@bigdata01 hadoop-3.2.0]# scp -rq etc/hadoop/hdfs-site.xml bigdata02:/data/soft/hadoop-3.2.0/etc/hadoop/ [root@bigdata01 hadoop-3.2.0]# scp -rq etc/hadoop/hdfs-site.xml bigdata03:/data/soft/hadoop-3.2.0/etc/hadoop/3：启动Hadoop集群[root@bigdata01 hadoop-3.2.0]# sbin/start-all.sh Starting namenodes on [bigdata01] Last login: Wed Apr 8 20:25:49 CST 2020 on pts/1 Starting datanodes Last login: Wed Apr 8 20:29:57 CST 2020 on pts/1 Starting secondary namenodes [bigdata01] Last login: Wed Apr 8 20:29:59 CST 2020 on pts/1 Starting resourcemanager Last login: Wed Apr 8 20:30:04 CST 2020 on pts/1 Starting nodemanagers Last login: Wed Apr 8 20:30:10 CST 2020 on pts/1重新再执行代码，没有报错，到hdfs上查看数据[root@bigdata01 hadoop-3.2.0]# hdfs dfs -ls / Found 4 items -rw-r--r-- 2 root supergroup 150569 2020-04-08 15:55 /LICENSE.txt -rw-r--r-- 2 root supergroup 22125 2020-04-08 15:55 /NOTICE.txt -rw-r--r-- 2 root supergroup 1361 2020-04-08 15:55 /README.txt -rw-r--r-- 3 yehua supergroup 17 2020-04-08 20:31 /user.txt [root@bigdata01 hadoop-3.2.0]# hdfs dfs -cat /user.txt jack tom jessic下面还需要实现其他功能，对代码进行封装提取package com.imooc.hdfs; import org.apache.hadoop.conf.Configuration; import org.apache.hadoop.fs.FSDataOutputStream; import org.apache.hadoop.fs.FileSystem; import org.apache.hadoop.fs.Path; import org.apache.hadoop.io.IOUtils; import java.io.FileInputStream; import java.io.IOException; import java.net.URI; /** * Java代码操作HDFS * 文件操作：上传文件、下载文件、删除文件 * Created by xuwei */ public class HdfsOp { public static void main(String[] args) throws Exception{ //创建一个配置对象 Configuration conf = new Configuration(); //指定HDFS的地址 conf.set("fs.defaultFS","hdfs://bigdata01:9000"); //获取操作HDFS的对象 FileSystem fileSystem = FileSystem.get(conf); put(fileSystem); } /** * 文件上传 * @param fileSystem * @throws IOException */ private static void put(FileSystem fileSystem) throws IOException { //获取HDFS文件系统的输出流 FSDataOutputStream fos = fileSystem.create(new Path("/user.txt")); //获取本地文件的输入流 FileInputStream fis = new FileInputStream("D:\\user.txt"); //上传文件：通过工具类把输入流拷贝到输出流里面，实现本地文件上传到HDFS IOUtils.copyBytes(fis,fos,1024,true); } }下载文件执行代码，到windows的D盘验证文件是否生成，如果有就表示执行成功/** * 下载文件 * @param fileSystem * @throws IOException */ private static void get(FileSystem fileSystem) throws IOException{ //获取HDFS文件系统的输入流 FSDataInputStream fis = fileSystem.open(new Path("/README.txt")); //获取本地文件的输出流 FileOutputStream fos = new FileOutputStream("D:\\README.txt"); //下载文件 IOUtils.copyBytes(fis,fos,1024,true); }删除文件执行删除操作代码/** * 删除文件 * @param fileSystem * @throws IOException */ private static void delete(FileSystem fileSystem) throws IOException{ //删除文件，目录也可以删除 //如果要递归删除目录，则第二个参数需要设置为true //如果是删除文件或者空目录，第二个参数会被忽略 boolean flag = fileSystem.delete(new Path("/LICENSE.txt"),true); if(flag){ System.out.println("删除成功！"); }else{ System.out.println("删除失败！"); } }然后到hdfs中验证文件是否被删除，从这里可以看出来/LICENSE.txt文件已经被删除[root@bigdata01 hadoop-3.2.0]# hdfs dfs -ls / Found 3 items -rw-r--r-- 2 root supergroup 22125 2020-04-08 15:55 /NOTICE.txt -rw-r--r-- 2 root supergroup 1361 2020-04-08 15:55 /README.txt -rw-r--r-- 3 yehua supergroup 17 2020-04-08 20:31 /user.txt我们在执行代码的时候会发现输出了很多红色的警告信息，虽然不影响代码执行，但是看起来很碍眼，强迫症实在忍不了SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder". SLF4J: Defaulting to no-operation (NOP) logger implementation SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details. log4j:WARN No appenders could be found for logger (org.apache.htrace.core.Tracer). log4j:WARN Please initialize the log4j system properly. log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.如何解决这个问题呢？通过分析错误信息发现第一个是缺少log4j的实现类，第二个是缺少log4j的配置文件1：pom.xml中增加log4j依赖<dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.7.10</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.7.10</version> </dependency>2：resources目录下添加log4j.properties文件在项目的src\main\resources目录中添加log4j.propertieslog4j.properties文件内容如下：log4j.rootLogger=info,stdout log4j.appender.stdout = org.apache.log4j.ConsoleAppender log4j.appender.stdout.Target = System.out log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS} [%t] [%c] [%p] - %m%n再执行代码，发现就不会有那些红色的警告信息了。HDFS体系结构前面我们掌握了HDFS的基本使用，下面我们来详细分析一下HDFS深层次的内容HDFS支持主从结构，主节点称为 NameNode ，是因为主节点上运行的有NameNode进程，NameNode支持多个，目前我们的集群中只配置了一个从节点称为 DataNode ，是因为从节点上面运行的有DataNode进程，DataNode支持多个，目前我们的集群中有两个HDFS中还包含一个 SecondaryNameNode 进程，这个进程从字面意思上看像是第二个NameNode的意思，其实不是，一会我们会详细分析。在这大家可以这样理解：公司BOSS：NameNode秘书：SecondaryNameNode员工：DataNode接着看一下这张图，这就是HDFS的体系结构，这里面的TCP、RPC、HTTP表示是不同的网络通信方式，通过这张图是想加深大家对HDFS体系结构的理解，我们前面配置的集群NameNode和SecondaryNameNode进程在同一台机器上面，在这个图里面是把它们分开到多台机器中了。那接下来我们就详细分析这里面的每一个进程。NameNode介绍首先是NameNode，NameNode是整个文件系统的管理节点它主要维护着整个文件系统的文件目录树，文件/目录的信息 和 每个文件对应的数据块列表，并且还负责接收用户的操作请求目录树：表示目录之间的层级关系，就是我们在hdfs上执行ls命令可以看到的那个目录结构信息。文件/目录的信息：表示文件/目录的的一些基本信息，所有者 属组 修改时间 文件大小等信息每个文件对应的数据块列表：如果一个文件太大，那么在集群中存储的时候会对文件进行切割，这个时候就类似于会给文件分成一块一块的，存储到不同机器上面。所以HDFS还要记录一下一个文件到底被分了多少块，每一块都在什么地方存储着我们现在可以到集群的9870界面查看一下，随便找一个文件看一下，点击文件名称，可以看到Block information 但是文件太小，只有一个块 叫Block 0我们试着上传一个大一点的文件，找一个200M左右的文件。[root@bigdata01 hadoop-3.2.0]# cd /data/soft/ [root@bigdata01 soft]# hdfs dfs -put hadoop-3.2.0.tar.gz /这个时候再去看 就能看到分成了多个Block块，一个文件对应有多少个Block块信息 是在namenode里面保存着的接收用户的操作请求：其实我们在命令行使用hdfs操作的时候，是需要先和namenode通信 才能开始去操作数据的。为什么呢？因为文件信息都在namenode上面存储着的namenode是非常重要的，它的这些信息最终是会存储到文件上的，那接下来我们来看一下NameNode中包含的那些文件NameNode主要包括以下文件：这些文件所在的路径是由hdfs-default.xml的dfs.namenode.name.dir属性控制的hdfs-default.xml文件在哪呢？它在hadoop-3.2.0\share\hadoop\hdfs\hadoop-hdfs-3.2.0.jar中，这个文件中包含了HDFS相关的所有默认参数，咱们在配置集群的时候会修改一个hdfs-site.xml文件，hdfs-site.xml文件属于hdfs-default.xml的一个扩展，它可以覆盖掉hdfs-default.xml中同名的参数。那我们来看一下这个文件中的dfs.namenode.name.dir属性<property> <name>dfs.namenode.name.dir</name> <value>file://${hadoop.tmp.dir}/dfs/name</value> <description>Determines where on the local filesystem the DFS name node should store the name table(fsimage). If this is a comma-delimited list of directories then the name table is replicated in all of the directories, for redundancy. </description> </property>这个属性的值是由hadoop.tmp.dir属性控制的，这个属性的值默认在core-default.xml文件中。大家还有没有印象，我们在修改core-site.xml的时候设置的有hadoop.tmp.dir属性的值，值是/data/hadoop_repo，所以说core-site.xml中的hadoop.tmp.dir属性会覆盖掉core-default.xml中的值最终dfs.namenode.name.dir属性的值就是：/data/hadoop_repo/dfs/name那我们到bigdata01节点上看一下进入到/data/hadoop_repo/dfs/name目录下发现这个下面会有一个current 目录，表示当前的意思，还有一个in_use.lock 这个只是一个普通文件，但是它其实有特殊的含义，你看他的文件名后缀值lock 表示是锁的意思，文件名是in_use 表示这个文件现在正在使用，不允许你再启动namenode。当我们启动namonde的时候 会判断这个目录下是否有in_use.lock 这个相当于一把锁，如果没有的话，才可以启动成功，启动成功之后就会加一把锁， 停止的时候会把这个锁去掉[root@bigdata01 name]# cd /data/hadoop_repo/dfs/name [root@bigdata01 name]# ll total 8 drwxr-xr-x. 2 root root 4096 Apr 8 21:31 current -rw-r--r--. 1 root root 14 Apr 8 20:30 in_use.lock [root@bigdata01 name]# cd current [root@bigdata01 current]# ll total 4152 -rw-r--r--. 1 root root 42 Apr 7 22:17 edits_0000000000000000001-0000000000000000002 -rw-r--r--. 1 root root 1048576 Apr 7 22:17 edits_0000000000000000003-0000000000000000003 -rw-r--r--. 1 root root 42 Apr 7 22:22 edits_0000000000000000004-0000000000000000005 -rw-r--r--. 1 root root 1048576 Apr 7 22:22 edits_0000000000000000006-0000000000000000006 -rw-r--r--. 1 root root 42 Apr 8 14:53 edits_0000000000000000007-0000000000000000008 -rw-r--r--. 1 root root 1644 Apr 8 15:53 edits_0000000000000000009-0000000000000000031 -rw-r--r--. 1 root root 1523 Apr 8 16:53 edits_0000000000000000032-0000000000000000051 -rw-r--r--. 1 root root 42 Apr 8 17:53 edits_0000000000000000052-0000000000000000053 -rw-r--r--. 1 root root 1048576 Apr 8 17:53 edits_0000000000000000054-0000000000000000054 -rw-r--r--. 1 root root 42 Apr 8 20:31 edits_0000000000000000055-0000000000000000056 -rw-r--r--. 1 root root 523 Apr 8 21:31 edits_0000000000000000057-0000000000000000065 -rw-r--r--. 1 root root 1048576 Apr 8 21:31 edits_inprogress_0000000000000000066 -rw-r--r--. 1 root root 652 Apr 8 20:31 fsimage_0000000000000000056 -rw-r--r--. 1 root root 62 Apr 8 20:31 fsimage_0000000000000000056.md5 -rw-r--r--. 1 root root 661 Apr 8 21:31 fsimage_0000000000000000065 -rw-r--r--. 1 root root 62 Apr 8 21:31 fsimage_0000000000000000065.md5 -rw-r--r--. 1 root root 3 Apr 8 21:31 seen_txid -rw-r--r--. 1 root root 219 Apr 8 20:30 VERSION里面有edits文件 和fsimage文件fsimage文件有两个文件名相同的，有一个后缀是md5 md5是一种加密算法，这个其实主要是为了做md5校验的，为了保证文件传输的过程中不出问题，相同内容的md5是一样的，所以后期如果我把这个fsimage和对应的fsimage.md5发给你 然后你根据md5对fsimage的内容进行加密，获取一个值 和fsimage.md5中的内容进行比较，如果一样，说明你接收到的文件就是完整的。我们在网站下载一些软件的时候 也会有一些md5文件，方便验证下载的文件是否完整。在这里可以把fsimage 拆开 fs 是文件系统 filesystem image是镜像说明是文件系统镜像，就是给文件照了一个像，把文件的当前信息记录下来我们可以看一下这个文件，这个文件需要使用特殊的命令进行查看-i 输入文件 -o 输出文件[root@bigdata01 current]# hdfs oiv -p XML -i fsimage_0000000000000000056 -o fsimage56.xml 2020-04-08 22:23:32,851 INFO offlineImageViewer.FSImageHandler: Loading 4 strings 2020-04-08 22:23:32,916 INFO namenode.FSDirectory: GLOBAL serial map: bits=29 maxEntries=536870911 2020-04-08 22:23:32,916 INFO namenode.FSDirectory: USER serial map: bits=24 maxEntries=16777215 2020-04-08 22:23:32,916 INFO namenode.FSDirectory: GROUP serial map: bits=24 maxEntries=16777215 2020-04-08 22:23:32,916 INFO namenode.FSDirectory: XATTR serial map: bits=24 maxEntries=16777215把fsimage56.xml这个文件拉取到window上，查看比较方便<?xml version="1.0"?> <fsimage><version><layoutVersion>-65</layoutVersion><onDiskVersion>1</onDiskVersion><oivRevision>e97acb3bd8f3befd27418996fa5d4b50bf2e17bf</oivRevision></version> <NameSection><namespaceId>498554338</namespaceId><genstampV1>1000</genstampV1><genstampV2>1005</genstampV2><genstampV1Limit>0</genstampV1Limit><lastAllocatedBlockId>1073741829</lastAllocatedBlockId><txid>56</txid></NameSection> <ErasureCodingSection> <erasureCodingPolicy> <policyId>5</policyId><policyName>RS-10-4-1024k</policyName><cellSize>1048576</cellSize><policyState>DISABLED</policyState><ecSchema> <codecName>rs</codecName><dataUnits>10</dataUnits><parityUnits>4</parityUnits></ecSchema> </erasureCodingPolicy> <erasureCodingPolicy> <policyId>2</policyId><policyName>RS-3-2-1024k</policyName><cellSize>1048576</cellSize><policyState>DISABLED</policyState><ecSchema> <codecName>rs</codecName><dataUnits>3</dataUnits><parityUnits>2</parityUnits></ecSchema> </erasureCodingPolicy> <erasureCodingPolicy> <policyId>1</policyId><policyName>RS-6-3-1024k</policyName><cellSize>1048576</cellSize><policyState>ENABLED</policyState><ecSchema> <codecName>rs</codecName><dataUnits>6</dataUnits><parityUnits>3</parityUnits></ecSchema> </erasureCodingPolicy> <erasureCodingPolicy> <policyId>3</policyId><policyName>RS-LEGACY-6-3-1024k</policyName><cellSize>1048576</cellSize><policyState>DISABLED</policyState><ecSchema> <codecName>rs-legacy</codecName><dataUnits>6</dataUnits><parityUnits>3</parityUnits></ecSchema> </erasureCodingPolicy> <erasureCodingPolicy> <policyId>4</policyId><policyName>XOR-2-1-1024k</policyName><cellSize>1048576</cellSize><policyState>DISABLED</policyState><ecSchema> <codecName>xor</codecName><dataUnits>2</dataUnits><parityUnits>1</parityUnits></ecSchema> </erasureCodingPolicy> </ErasureCodingSection> <INodeSection><lastInodeId>16395</lastInodeId><numInodes>4</numInodes><inode><id>16385</id><type>DIRECTORY</type><name></name><mtime>1586332531935</mtime><permission>root:supergroup:0755</permission><nsquota>9223372036854775807</nsquota><dsquota>-1</dsquota></inode> <inode><id>16393</id><type>FILE</type><name>LICENSE.txt</name><replication>2</replication><mtime>1586332513657</mtime><atime>1586332513485</atime><preferredBlockSize>134217728</preferredBlockSize><permission>root:supergroup:0644</permission><blocks><block><id>1073741827</id><genstamp>1003</genstamp><numBytes>150569</numBytes></block> </blocks> <storagePolicyId>0</storagePolicyId></inode> <inode><id>16394</id><type>FILE</type><name>NOTICE.txt</name><replication>2</replication><mtime>1586332522962</mtime><atime>1586332522814</atime><preferredBlockSize>134217728</preferredBlockSize><permission>root:supergroup:0644</permission><blocks><block><id>1073741828</id><genstamp>1004</genstamp><numBytes>22125</numBytes></block> </blocks> <storagePolicyId>0</storagePolicyId></inode> <inode><id>16395</id><type>FILE</type><name>README.txt</name><replication>2</replication><mtime>1586332531932</mtime><atime>1586332531689</atime><preferredBlockSize>134217728</preferredBlockSize><permission>root:supergroup:0644</permission><blocks><block><id>1073741829</id><genstamp>1005</genstamp><numBytes>1361</numBytes></block> </blocks> <storagePolicyId>0</storagePolicyId></inode> </INodeSection> <INodeReferenceSection></INodeReferenceSection><SnapshotSection><snapshotCounter>0</snapshotCounter><numSnapshots>0</numSnapshots></SnapshotSection> <INodeDirectorySection><directory><parent>16385</parent><child>16393</child><child>16394</child><child>16395</child></directory> </INodeDirectorySection> <FileUnderConstructionSection></FileUnderConstructionSection> <SecretManagerSection><currentId>0</currentId><tokenSequenceNumber>0</tokenSequenceNumber><numDelegationKeys>0</numDelegationKeys><numTokens>0</numTokens></SecretManagerSection><CacheManagerSection><nextDirectiveId>1</nextDirectiveId><numDirectives>0</numDirectives><numPools>0</numPools></CacheManagerSection> </fsimage>里面最外层是一个fsimage标签，看里面的inode标签，这个inode表示是hdfs中的每一个目录或者文件信息例如这个：<inode><id>16393</id><type>FILE</type><name>LICENSE.txt</name><replication>2</replication><mtime>1586332513657</mtime><atime>1586332513485</atime><preferredBlockSize>134217728</preferredBlockSize><permission>root:supergroup:0644</permission><blocks><block><id>1073741827</id><genstamp>1003</genstamp><numBytes>150569</numBytes></block> </blocks> <storagePolicyId>0</storagePolicyId></inode>id：唯一编号type：文件类型name：文件名称replication：文件的副本数量mtime：修改时间atime：访问时间preferredBlockSize：推荐每一个数据块的大小permission：权限信息blocks：包含多少数据块【文件被切成数据块】block：内部的id表示是块id，genstamp是一个唯一编号，numBytes表示当前数据块的实际大小，storagePolicyId表示是数据的存储策略这个文件中其实就维护了整个文件系统的文件目录树，文件/目录的元信息和每个文件对应的数据块列表，所以说fsimage中存放了hdfs最核心的数据。下面我们来看一下edits文件，这些文件在这称之为事务文件，为什么呢？当我们上传一个文件的时候，上传一个10G的文件，假设传到9G的时候上传失败了，这个时候就需要重新传，那hdfs怎么知道这个文件失败了呢？这个是在edits文件中记录的。当我们上传大文件的时候，一个大文件会分为多个block，那么edits文件中就会记录这些block的上传状态，只有当全部block都上传成功了以后，这个时候edits中才会记录这个文件上传成功了，那么我们执行hdfs dfs -ls 的时候就能查到这个文件了，所以当我们在hdfs中执行ls命令的时候，其实会查询fsimage和edits中的内容为什么会有这两个文件呢？首先,我们固化的一些文件内容是存储在fsimage文件中，当前正在上传的文件信息是存储在edits文件中。 这个时候我们来查看一下这个edits文件的内容，挑一个edits文件内容多一些的文件[root@bigdata01 current]# hdfs oev -i edits_0000000000000000057-0000000000000000065 -o edits.xml分析生成的edits.xml文件，这个地方注意，可能有的edits文件生成的edits.xml为空，需要多试几个。这个edits.xml中可以大致看一下，里面有很多record。每一个record代表不同的操作，例如 OP_ADD,OP_CLOSE 等等，具体挑一个实例进行分析。OP_ADD：执行上传操作OP_ALLOCATE_BLOCK_ID：申请block块idOP_SET_GENSTAMP_V2：设置GENSTAMPOP_ADD_BLOCK：添加block块OP_CLOSE：关闭上传操作<RECORD> <OPCODE>OP_ADD</OPCODE> <DATA> <TXID>58</TXID> <LENGTH>0</LENGTH> <INODEID>16396</INODEID> <PATH>/user.txt</PATH> <REPLICATION>3</REPLICATION> <MTIME>1586349095694</MTIME> <ATIME>1586349095694</ATIME> <BLOCKSIZE>134217728</BLOCKSIZE> <CLIENT_NAME>DFSClient_NONMAPREDUCE_-1768454371_1</CLIENT_NAME> <CLIENT_MACHINE>192.168.182.1</CLIENT_MACHINE> <OVERWRITE>true</OVERWRITE> <PERMISSION_STATUS> <USERNAME>yehua</USERNAME> <GROUPNAME>supergroup</GROUPNAME> <MODE>420</MODE> </PERMISSION_STATUS> <ERASURE_CODING_POLICY_ID>0</ERASURE_CODING_POLICY_ID> <RPC_CLIENTID>1722b83a-2dc7-4c46-baa9-9fa956b755cd</RPC_CLIENTID> <RPC_CALLID>0</RPC_CALLID> </RECORD> <RECORD> <OPCODE>OP_ALLOCATE_BLOCK_ID</OPCODE> <DATA> <TXID>59</TXID> <BLOCK_ID>1073741830</BLOCK_ID> </RECORD> <RECORD> <OPCODE>OP_SET_GENSTAMP_V2</OPCODE> <DATA> <TXID>60</TXID> <GENSTAMPV2>1006</GENSTAMPV2> </RECORD> <RECORD> <OPCODE>OP_ADD_BLOCK</OPCODE> <DATA> <TXID>61</TXID> <PATH>/user.txt</PATH> <BLOCK> <BLOCK_ID>1073741830</BLOCK_ID> <NUM_BYTES>0</NUM_BYTES> <GENSTAMP>1006</GENSTAMP> </BLOCK> <RPC_CLIENTID/> <RPC_CALLID>-2</RPC_CALLID> </RECORD> <RECORD> <OPCODE>OP_CLOSE</OPCODE> <DATA> <TXID>62</TXID> <LENGTH>0</LENGTH> <INODEID>0</INODEID> <PATH>/user.txt</PATH> <REPLICATION>3</REPLICATION> <MTIME>1586349096480</MTIME> <ATIME>1586349095694</ATIME> <BLOCKSIZE>134217728</BLOCKSIZE> <CLIENT_NAME/> <CLIENT_MACHINE/> <OVERWRITE>false</OVERWRITE> <BLOCK> <BLOCK_ID>1073741830</BLOCK_ID> <NUM_BYTES>17</NUM_BYTES> <GENSTAMP>1006</GENSTAMP> </BLOCK> <PERMISSION_STATUS> <USERNAME>yehua</USERNAME> <GROUPNAME>supergroup</GROUPNAME> <MODE>420</MODE> </PERMISSION_STATUS> </RECORD>这里面的每一个record都有一个事务id，txid，事务id是连续的，其实一个put操作会在edits文件中产生很多的record，对应的就是很多步骤，这些步骤对我们是屏蔽的。注意了，根据我们刚才的分析，我们所有对hdfs的增删改操作都会在edits文件中留下信息，那么fsimage文件中的内容是从哪来的？其实是这样的，edits文件会定期合并到fsimage文件中。有同学可能有疑问了，edits文件和fsimage文件中的内容是不一样的，这怎么能是合并出来的呢？注意，这个其实是框架去做的，在合并的时候会对edits中的内容进行转换，生成新的内容，其实edits中保存的内容是不是太细了，单单一个上传操作就分为了好几步，其实上传成功之后，我们只需要保存文件具体存储的block信息就行了把，所以在合并的时候其实是对edits中的内容进行了精简。他们具体合并的代码我们不用太过关注，但是我们要知道是那个进程去做的这个事情，其实就是我们之前提到的secondarynamenode这个进程就是负责定期的把edits中的内容合并到fsimage中。他只做一件事，这是一个单独的进程，在实际工作中部署的时候，也需要部署到一个单独的节点上面。current目录中还有一个seentxid文件，HDFS format之后是0，它代表的是namenode里面的edits*文件的尾数,namenode重启的时候，会按照seen_txid的数字，顺序从头跑edits_0000001~到seen_txid的数字。如果根据对应的seen_txid无法加载到对应的文件，NameNode进程将不会完成启动以保护数据一致性。[root@bigdata01 current]# cat seen_txid 66最后这个current目录下面还有一个VERSION文件[root@bigdata01 current]# cat VERSION #Wed Apr 08 20:30:00 CST 2020 namespaceID=498554338 clusterID=CID-cc0792dd-a861-4a3f-9151-b0695e4c7e70 cTime=1586268855170 storageType=NAME_NODE blockpoolID=BP-1517789416-192.168.182.100-1586268855170 layoutVersion=-65这里面显示的集群的一些信息、当重新对hdfs格式化 之后，这里面的信息会变化。之前我们说过 在使用hdfs的时候只格式化一次，不要格式化多次，为什么呢？一会在讲datanode的时候会详细解释、最后做一个总结：fsimage: 元数据镜像文件，存储某一时刻NameNode内存中的元数据信息，就类似是定时做了一个快照操作。【这里的元数据信息是指文件目录树、文件/目录的信息、每个文件对应的数据块列表】edits: 操作日志文件【事务文件】，这里面会实时记录用户的所有操作seentxid: 是存放transactionId的文件，format之后是0，它代表的是namenode里面的edits*文件的尾数,namenode重启的时候，会按照seen_txid的数字，顺序从头跑edits_0000001~到seen_txid的数字。如果根据对应的seen_txid无法加载到对应的文件，NameNode进程将不会完成启动以保护数据一致性。VERSION:保存了集群的版本信息SecondaryNameNode介绍刚才在分析edits日志文件的时候我们已经针对SecondaryNameNode做了介绍，在这里再做一个总结，以示重视。SecondaryNameNode主要负责定期的把edits文件中的内容合并到fsimage中这个合并操作称为checkpoint，在合并的时候会对edits中的内容进行转换，生成新的内容保存到fsimage文件中。注意：在NameNode的HA架构中没有SecondaryNameNode进程，文件合并操作会由standby NameNode负责实现所以在Hadoop集群中，SecondaryNameNode进程并不是必须的。DataNode介绍DataNode是提供真实文件数据的存储服务针对datanode主要掌握两个概念，一个是block，一个是replication首先是blockHDFS会按照固定的大小，顺序对文件进行划分并编号，划分好的每一个块称一个Block，HDFS默认Block大小是 128MBBlokc块是HDFS读写数据的基本单位，不管你的文件是文本文件 还是视频 或者音频文件，针对hdfs而言 都是字节。我们之前上传的一个user.txt文件，他的block信息可以在fsimage文件中看到，也可以在hdfs webui上面看到, 里面有block的id信息,并且也会显示这个数据在哪个节点上面这里显示在bigdata02和bigdata03上面都有，那我们过去看一下，datanode中数据的具体存储位置是由dfs.datanode.data.dir来控制的，通过查询hdfs-default.xml可以知道，具体的位置在这里<property> <name>dfs.datanode.data.dir</name> <value>file://${hadoop.tmp.dir}/dfs/data</value> <description>Determines where on the local filesystem an DFS data node should store its blocks. If this is a comma-delimited list of directories, then data will be stored in all named directories, typically on different devices. The directories should be tagged with corresponding storage types ([SSD]/[DISK]/[ARCHIVE]/[RAM_DISK]) for HDFS storage policies. The default storage type will be DISK if the directory does not have a storage type tagged explicitly. Directories that do not exist will be created if local filesystem permission allows. </description> </property>那我们连接到bigdata02这个节点上去看一下[root@bigdata02 ~]# cd /data/hadoop_repo/dfs/data/ [root@bigdata02 data]# ll total 4 drwxr-xr-x. 3 root root 72 Apr 7 22:21 current -rw-r--r--. 1 root root 14 Apr 8 20:30 in_use.lock然后进入current目录，继续一路往下走[root@bigdata02 data]# cd current/ [root@bigdata02 current]# ll total 4 drwx------. 4 root root 54 Apr 8 20:30 BP-1517789416-192.168.182.100-1586268855170 -rw-r--r--. 1 root root 229 Apr 8 20:30 VERSION [root@bigdata02 current]# cd BP-1517789416-192.168.182.100-1586268855170/ [root@bigdata02 BP-1517789416-192.168.182.100-1586268855170]# ll total 4 drwxr-xr-x. 4 root root 64 Apr 8 20:25 current -rw-r--r--. 1 root root 166 Apr 7 22:21 scanner.cursor drwxr-xr-x. 2 root root 6 Apr 8 20:30 tmp [root@bigdata02 BP-1517789416-192.168.182.100-1586268855170]# cd current/ [root@bigdata02 current]# ll total 8 -rw-r--r--. 1 root root 20 Apr 8 20:25 dfsUsed drwxr-xr-x. 3 root root 21 Apr 8 15:34 finalized drwxr-xr-x. 2 root root 6 Apr 8 22:13 rbw -rw-r--r--. 1 root root 146 Apr 8 20:30 VERSION [root@bigdata02 current]# cd finalized/ [root@bigdata02 finalized]# ll total 0 drwxr-xr-x. 3 root root 21 Apr 8 15:34 subdir0 [root@bigdata02 finalized]# cd subdir0/ [root@bigdata02 subdir0]# ll total 4 drwxr-xr-x. 2 root root 4096 Apr 8 22:13 subdir0 [root@bigdata02 subdir0]# cd subdir0/ [root@bigdata02 subdir0]# ll total 340220 -rw-r--r--. 1 root root 22125 Apr 8 15:55 blk_1073741828 -rw-r--r--. 1 root root 183 Apr 8 15:55 blk_1073741828_1004.meta -rw-r--r--. 1 root root 1361 Apr 8 15:55 blk_1073741829 -rw-r--r--. 1 root root 19 Apr 8 15:55 blk_1073741829_1005.meta -rw-r--r--. 1 root root 17 Apr 8 20:31 blk_1073741830 -rw-r--r--. 1 root root 11 Apr 8 20:31 blk_1073741830_1006.meta -rw-r--r--. 1 root root 134217728 Apr 8 22:13 blk_1073741831 -rw-r--r--. 1 root root 1048583 Apr 8 22:13 blk_1073741831_1007.meta -rw-r--r--. 1 root root 134217728 Apr 8 22:13 blk_1073741832 -rw-r--r--. 1 root root 1048583 Apr 8 22:13 blk_1073741832_1008.meta -rw-r--r--. 1 root root 77190019 Apr 8 22:13 blk_1073741833 -rw-r--r--. 1 root root 603055 Apr 8 22:13 blk_1073741833_1009.meta这里面就有很多的block块了，注意: 这里面的.meta文件也是做校验用的。根据前面看到的blockid信息到这对应的找到文件，可以直接查看，发现文件内容就是我们之前上传上去的内容。[root@bigdata02 subdir0]# cat blk_1073741830 jack tom jessic [root@bigdata02 subdir0]#注意：这个block中的内容可能只是文件的一部分，如果你的文件较大的话，就会分为多个block存储，默认 hadoop3中一个block的大小为128M。根据字节进行截取，截取到128M就是一个block。如果文件大小没有默认的block块大，那最终就只有一个block。HDFS中，如果一个文件小于一个数据块的大小，那么并不会占用整个数据块的存储空间size是表示我们上传文件的实际大小，blocksize是指文件的最大块大小。注意；这个block块是hdfs产生的，如果我们直接把文件上传到这个block文件所在的目录，这个时候hdfs是不识别的，没有用的假设我们上传了两个10M的文件 又上传了一个200M的文件问1：会产生多少个block块？ 4个问2：在hdfs中会显示几个文件？3个下面看一下副本，副本表示数据有多少个备份我们现在的集群有两个从节点，所以最多可以有2个备份，这个是在hdfs-site.xml中进行配置的，dfs.replication默认这个参数的配置是3。表示会有3个副本。副本只有一个作用就是保证数据安全。NameNode总结注意：block块存放在哪些datanode上，只有datanode自己知道，当集群启动的时候，datanode会扫描自己节点上面的所有block块信息，然后把节点和这个节点上的所有block块信息告诉给namenode。这个关系是每次重启集群都会动态加载的【这个其实就是集群为什么数据越多，启动越慢的原因】咱们之前说的fsimage(edits)文件中保存的有文件和block块之间的信息。这里说的是block块和节点之间的关系，这两块关联在一起之后，就可以根据文件找到对应的block块，再根据block块找到对应的datanode节点，这样就真正找到了数据。所以说 其实namenode中不仅维护了文件和block块的信息 还维护了block块和所在的datanode节点的信息。可以理解为namenode维护了两份关系：第一份关系：file 与block list的关系，对应的关系信息存储在fsimage和edits文件中,当NameNode启动的时候会把文件中的元数据信息加载到内存中第二份关系：datanode与block的关系，对应的关系主要在集群启动的时候保存在内存中,当DataNode启动时会把当前节点上的Block信息和节点信息上报给NameNode注意了，刚才我们说了NameNode启动的时候会把文件中的元数据信息加载到内存中，然后每一个文件的元数据信息会占用150字节的内存空间，这个是恒定的，和文件大小没有关系，咱们前面在介绍HDFS的时候说过，HDFS不适合存储小文件，其实主要原因就在这里，不管是大文件还是小文件，一个文件的元数据信息在NameNode中都会占用150字节，NameNode节点的内存是有限的，所以它的存储能力也是有限的，如果我们存储了一堆都是几KB的小文件，最后发现NameNode的内存占满了，确实存储了很多文件，但是文件的总体大小却很小，这样就失去了HDFS存在的价值最后，在datanode的数据目录下面的current目录中也有一个VERSION文件这个VERSION和namenode的VERSION文件是有一些相似之处的，我们来具体对比一下两个文件的内容。namenode的VERSION文件[root@bigdata01 current]# cat VERSION #Wed Apr 08 20:30:00 CST 2020 namespaceID=498554338 clusterID=CID-cc0792dd-a861-4a3f-9151-b0695e4c7e70 cTime=1586268855170 storageType=NAME_NODE blockpoolID=BP-1517789416-192.168.182.100-1586268855170 layoutVersion=-65datanode的VERSION文件[root@bigdata02 current]# cat VERSION #Wed Apr 08 20:30:04 CST 2020 storageID=DS-0e86cd27-4961-4526-bacb-3b692a90b1b0 clusterID=CID-cc0792dd-a861-4a3f-9151-b0695e4c7e70 cTime=0 datanodeUuid=0b09f3d7-442d-4e28-b3cc-2edb0991bae3 storageType=DATA_NODE layoutVersion=-57我们前面说了namenode不要随便格式化，因为格式化了以后VERSION里面的clusterID会变，但是datanode的VERSION中的clusterID并没有变，所以就对应不上了。咱们之前说过如果确实要重新格式化的话需要把/data/hadoop_repo数据目录下的内容都清空，全部都重新生成是可以的。HDFS的回收站我们windows系统里面有一个回收站，当想恢复删除的文件的话就可以到这里面进行恢复，HDFS也有回收站。HDFS会为每一个用户创建一个回收站目录：/user/用户名/.Trash/，每一个被用户在Shell命令行删除的文件/目录，会进入到对应的回收站目录中，在回收站中的数据都有一个生存周期，也就是当回收站中的文件/目录在一段时间之内没有被用户恢复的话，HDFS就会自动的把这个文件/目录彻底删除，之后，用户就永远也找不回这个文件/目录了。默认情况下hdfs的回收站是没有开启的，需要通过一个配置来开启，在core-site.xml中添加如下配置，value的单位是分钟，1440分钟表示是一天的生存周期<property> <name>fs.trash.interval</name> <value>1440</value> </property>在修改配置信息之前先验证一下删除操作，显示的是直接删除掉了。[root@bigdata01 hadoop-3.2.0]# hdfs dfs -rm -r /NOTICE.txt Deleted /NOTICE.txt修改回收站配置，先在bigdata01上操作，然后再同步到其它两个节点，先停止集群[root@bigdata01 hadoop-3.2.0]# sbin/stop-all.sh [root@bigdata01 hadoop-3.2.0]# vi etc/hadoop/core-site.xml <configuration> <property> <name>fs.defaultFS</name> <value>hdfs://bigdata01:9000</value> </property> <property> <name>hadoop.tmp.dir</name> <value>/data/hadoop_repo</value> </property> <property> <name>fs.trash.interval</name> <value>1440</value> </property> </configuration> [root@bigdata01 hadoop-3.2.0]# scp -rq etc/hadoop/core-site.xml bigdata02:/data/soft/hadoop-3.2.0/etc/hadoop/ [root@bigdata01 hadoop-3.2.0]# scp -rq etc/hadoop/core-site.xml bigdata03:/data/soft/hadoop-3.2.0/etc/hadoop/启动集群，再执行删除操作[root@bigdata01 hadoop-3.2.0]# sbin/start-all.sh [root@bigdata01 hadoop-3.2.0]# hdfs dfs -rm -r /README.txt 2020-04-09 11:43:47,664 INFO fs.TrashPolicyDefault: Moved: 'hdfs://bigdata01:9000/README.txt' to trash at: hdfs://bigdata01:9000/user/root/.Trash/Current/README.txt此时看到提示信息说把删除的文件移到到了指定目录中，其实就是移动到了当前用户的回收站目录。回收站的文件也是可以下载到本地的。其实在这回收站只是一个具备了特殊含义的HDFS目录。注意：如果删除的文件过大，超过回收站大小的话会提示删除失败 需要指定参数 -skipTrash ，指定这个参数表示删除的文件不会进回收站[root@bigdata01 hadoop-3.2.0]# hdfs dfs -rm -r -skipTrash /user.txt Deleted /user.txtHDFS的安全模式大家在平时操作HDFS的时候，有时候可能会遇到这个问题，特别是刚启动集群的时候去上传或者删除文件，会发现报错，提示NameNode处于safe mode。这个属于HDFS的安全模式，因为在集群每次重新启动的时候，HDFS都会检查集群中文件信息是否完整，例如副本是否缺少之类的信息，所以这个时间段内是不允许对集群有修改操作的，如果遇到了这个情况，可以稍微等一会，等HDFS自检完毕，就会自动退出安全模式。[root@bigdata01 hadoop-3.2.0]# hdfs dfs -rm -r /hadoop-3.2.0.tar.gz 2020-04-09 12:00:36,646 WARN fs.TrashPolicyDefault: Can't create trash directory: hdfs://bigdata01:9000/user/root/.Trash/Current org.apache.hadoop.hdfs.server.namenode.SafeModeException: Cannot create directory /user/root/.Trash/Current. Name node is in safe mode.此时访问HDFS的web ui界面，可以看到下面信息，on表示处于安全模式，off表示安全模式已结束或者通过hdfs命令也可以查看当前的状态[root@bigdata01 hadoop-3.2.0]# hdfs dfsadmin -safemode get Safe mode is ON如果想快速离开安全模式，可以通过命令强制离开，正常情况下建议等HDFS自检完毕，自动退出[root@bigdata01 hadoop-3.2.0]# hdfs dfsadmin -safemode leave Safe mode is OFF此时，再操作HDFS中的文件就可以了。
MySQL 事务日志(redo log和undo log)innodb事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志： 1.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。 2.undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。1.redo log1.1 redo log和二进制日志的区别二进制日志相关内容，参考：。redo log不是二进制日志。虽然二进制日志中也记录了innodb表的很多操作，也能实现重做的功能，但是它们之间有很大区别。二进制日志是在存储引擎的上层产生的，不管是什么存储引擎，对数据库进行了修改都会产生二进制日志。而redo log是innodb层产生的，只记录该存储引擎中表的修改。并且二进制日志先于redo log被记录。具体的见后文group commit小结。二进制日志记录操作的方法是逻辑性的语句。即便它是基于行格式的记录方式，其本质也还是逻辑的SQL设置，如该行记录的每列的值是多少。而redo log是在物理格式上的日志，它记录的是数据库中每个页的修改。二进制日志只在每次事务提交的时候一次性写入缓存中的日志”文件”(对于非事务表的操作，则是每次执行语句成功后就直接写入)。而redo log在数据准备修改前写入缓存中的redo log中，然后才对缓存中的数据执行修改操作；而且保证在发出事务提交指令时，先向缓存中的redo log写入日志，写入完成后才执行提交动作。因为二进制日志只在提交的时候一次性写入，所以二进制日志中的记录方式和提交顺序有关，且一次提交对应一次记录。而redo log中是记录的物理页的修改，redo log文件中同一个事务可能多次记录，最后一个提交的事务记录会覆盖所有未提交的事务记录。例如事务T1，可能在redo log中记录了 T1-1,T1-2,T1-3，T1 共4个操作，其中 T1 表示最后提交时的日志记录，所以对应的数据页最终状态是 T1* 对应的操作结果。而且redo log是并发写入的，不同事务之间的不同版本的记录会穿插写入到redo log文件中，例如可能redo log的记录方式如下： T1-1,T1-2,T2-1,T2-2,T2。事务日志记录的是物理页的情况，它具有幂等性，因此记录日志的方式极其简练。幂等性的意思是多次操作前后状态是一样的，例如新插入一行后又删除该行，前后状态没有变化。而二进制日志记录的是所有影响数据的操作，记录的内容较多。例如插入一行记录一次，删除该行又记录一次。1.2 redo log的基本概念redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。在概念上，innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)。因为MariaDB/MySQL是工作在用户空间的，MariaDB/MySQL的log buffer处于用户空间的内存中。要写入到磁盘上的log file中(redo:ib_logfileN文件,undo:share tablespace或.ibd文件)，中间还要经过操作系统内核空间的os buffer，调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中。也就是说，从redo log buffer写日志到磁盘的redo log file中，过程如下：在此处需要注意一点，一般所说的log file并不是磁盘上的物理日志文件，而是操作系统缓存中的log file，官方手册上的意思也是如此(例如：With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second)。但说实话，这不太好理解，既然都称为file了，应该已经属于物理文件了。所以在本文后续内容中都以os buffer或者file system buffer来表示官方手册中所说的Log file，然后log file则表示磁盘上的物理日志文件，即log file on disk。 另外，之所以要经过一层os buffer，是因为open日志文件的时候，open没有使用O_DIRECT标志位，该标志位意味着绕过操作系统层的os buffer，IO直写到底层存储设备。不使用该标志位意味着将日志进行缓冲，缓冲到了一定容量，或者显式fsync()才会将缓冲中的刷到存储设备。使用该标志位意味着每次都要发起系统调用。比如写abcde，不使用o_direct将只发起一次系统调用，使用o_object将发起5次系统调用。MySQL支持用户自定义在commit时如何将log buffer中的日志刷log file中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有3种值：0、1、2，默认为1。但注意，这个变量只是控制commit动作是否刷新log buffer到磁盘。当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。注意，有一个变量 innodb_flush_log_at_timeout 的值为1秒，该变量表示的是刷日志的频率，很多人误以为是控制 innodb_flush_log_at_trx_commit 值为0和2时的1秒频率，实际上并非如此。测试时将频率设置为5和设置为1，当 innodb_flush_log_at_trx_commit 设置为0和2的时候性能基本都是不变的。关于这个频率是控制什么的，在后面的”刷日志到磁盘的规则”中会说。在主从复制结构中，要保证事务的持久性和一致性，需要对日志相关变量设置为如下：如果启用了二进制日志，则设置sync_binlog=1，即每提交一次事务同步写到磁盘中。总是设置innodb_flush_log_at_trx_commit=1，即每提交一次事务都写到磁盘中。上述两项变量的设置保证了：每次提交事务都写入二进制日志和事务日志，并在提交时将它们刷新到磁盘中。选择刷日志的时间会严重影响数据修改时的性能，特别是刷到磁盘的过程。下例就测试了 innodb_flush_log_at_trx_commit 分别为0、1、2时的差距。#创建测试表 drop table if exists test_flush_log; create table test_flush_log(id int,name char(50))engine=innodb; #创建插入指定行数的记录到测试表中的存储过程 drop procedure if exists proc; delimiter $$ create procedure proc(i int) begin declare s int default 1; declare c char(50) default repeat('a',50); while s<=i do start transaction; insert into test_flush_log values(null,c); commit; set s=s+1; end while; end$$ delimiter ;当前环境下， innodb_flush_log_at_trx_commit 的值为1，即每次提交都刷日志到磁盘。测试此时插入10W条记录的时间。mysql> call proc(100000); Query OK, 0 rows affected (15.48 sec)结果是15.48秒。再测试值为2的时候，即每次提交都刷新到os buffer，但每秒才刷入磁盘中。mysql> set @@global.innodb_flush_log_at_trx_commit=2; mysql> truncate test_flush_log; mysql> call proc(100000); Query OK, 0 rows affected (3.41 sec)结果插入时间大减，只需3.41秒。最后测试值为0的时候，即每秒才刷到os buffer和磁盘。mysql> set @@global.innodb_flush_log_at_trx_commit=0; mysql> truncate test_flush_log; mysql> call proc(100000); Query OK, 0 rows affected (2.10 sec)结果只有2.10秒。最后可以发现，其实值为2和0的时候，它们的差距并不太大，但2却比0要安全的多。它们都是每秒从os buffer刷到磁盘，它们之间的时间差体现在log buffer刷到os buffer上。因为将log buffer中的日志刷新到os buffer只是内存数据的转移，并没有太大的开销，所以每次提交和每秒刷入差距并不大。可以测试插入更多的数据来比较，以下是插入100W行数据的情况。从结果可见，值为2和0的时候差距并不大，但值为1的性能却差太多。尽管设置为0和2可以大幅度提升插入性能，但是在故障的时候可能会丢失1秒钟数据，这1秒钟很可能有大量的数据，从上面的测试结果看，100W条记录也只消耗了20多秒，1秒钟大约有4W-5W条数据，尽管上述插入的数据简单，但却说明了数据丢失的大量性。更好的插入数据的做法是将值设置为1，然后修改存储过程，将每次循环都提交修改为只提交一次，**这样既能保证数据的一致性，也能提升性能，修改如下：drop procedure if exists proc; delimiter $$ create procedure proc(i int) begin declare s int default 1; declare c char(50) default repeat('a',50); start transaction; while s<=i DO insert into test_flush_log values(null,c); set s=s+1; end while; commit; end$$ delimiter ;测试值为1时的情况。mysql> set @@global.innodb_flush_log_at_trx_commit=1; mysql> truncate test_flush_log; mysql> call proc(1000000); Query OK, 0 rows affected (11.26 sec)1.3 日志块(log block)innodb存储引擎中，redo log以块为单位进行存储的，每个块占512字节，这称为redo log block。所以不管是log buffer中还是os buffer中以及redo log file on disk中，都是这样以512字节的块存储的。每个redo log block由3部分组成：日志块头、日志块尾和日志主体。其中日志块头占用12字节，日志块尾占用8字节，所以每个redo log block的日志主体部分只有512-12-8=492字节。因为redo log记录的是数据页的变化，当一个数据页产生的变化需要使用超过492字节()的redo log来记录，那么就会使用多个redo log block来记录该数据页的变化。日志块头包含4部分： log_block_hdr_no：(4字节)该日志块在redo log buffer中的位置ID。 log_block_hdr_data_len：(2字节)该log block中已记录的log大小。写满该log block时为0x200，表示512字节。 log_block_first_rec_group：(2字节)该log block中第一个log的开始偏移位置。 lock_block_checkpoint_no：(4字节)写入检查点信息的位置。关于log block块头的第三部分 log_block_first_rec_group ，因为有时候一个数据页产生的日志量超出了一个日志块，这是需要用多个日志块来记录该页的相关日志。例如，某一数据页产生了552字节的日志量，那么需要占用两个日志块，第一个日志块占用492字节，第二个日志块需要占用60个字节，那么对于第二个日志块来说，它的第一个log的开始位置就是73字节(60+12)。如果该部分的值和 log_block_hdr_data_len 相等，则说明该log block中没有新开始的日志块，即表示该日志块用来延续前一个日志块。日志尾只有一个部分： log_block_trl_no ，该值和块头的 log_block_hdr_no 相等。上面所说的是一个日志块的内容，在redo log buffer或者redo log file on disk中，由很多log block组成。如下图：1.4 log group和redo log filelog group表示的是redo log group，一个组内由多个大小完全相同的redo log file组成。组内redo log file的数量由变量 innodb_log_files_group 决定，默认值为2，即两个redo log file。这个组是一个逻辑的概念，并没有真正的文件来表示这是一个组，但是可以通过变量 innodb_log_group_home_dir 来定义组的目录，redo log file都放在这个目录下，默认是在datadir下。mysql> show global variables like "innodb_log%"; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | innodb_log_buffer_size | 8388608 | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 50331648 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | +-----------------------------+----------+ [root@xuexi data]# ll /mydata/data/ib* -rw-rw---- 1 mysql mysql 79691776 Mar 30 23:12 /mydata/data/ibdata1 -rw-rw---- 1 mysql mysql 50331648 Mar 30 23:12 /mydata/data/ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Mar 30 23:12 /mydata/data/ib_logfile1可以看到在默认的数据目录下，有两个ib_logfile开头的文件，它们就是log group中的redo log file，而且它们的大小完全一致且等于变量 innodb_log_file_size 定义的值。第一个文件ibdata1是在没有开启 innodb_file_per_table 时的共享表空间文件，对应于开启 innodb_file_per_table 时的.ibd文件。在innodb将log buffer中的redo log block刷到这些log file中时，会以追加写入的方式循环轮训写入。即先在第一个log file（即ib_logfile0）的尾部追加写，直到满了之后向第二个log file（即ib_logfile1）写。当第二个log file满了会清空一部分第一个log file继续写入。由于是将log buffer中的日志刷到log file，所以在log file中记录日志的方式也是log block的方式。在每个组的第一个redo log file中，前2KB记录4个特定的部分，从2KB之后才开始记录log block。除了第一个redo log file中会记录，log group中的其他log file不会记录这2KB，但是却会腾出这2KB的空间。如下：redo log file的大小对innodb的性能影响非常大，设置的太大，恢复的时候就会时间较长，设置的太小，就会导致在写redo log的时候循环切换redo log file。1.5 redo log的格式因为innodb存储引擎存储数据的单元是页(和SQL Server中一样)，所以redo log也是基于页的格式来记录的。默认情况下，innodb的页大小是16KB(由 innodb_page_size 变量控制)，一个页内可以存放非常多的log block(每个512字节)，而log block中记录的又是数据页的变化。其中log block中492字节的部分是log body，该log body的格式分为4部分：redo_log_type：占用1个字节，表示redo log的日志类型。space：表示表空间的ID，采用压缩的方式后，占用的空间可能小于4字节。page_no：表示页的偏移量，同样是压缩过的。redo_log_body表示每个重做日志的数据部分，恢复时会调用相应的函数进行解析。例如insert语句和delete语句写入redo log的内容是不一样的。如下图，分别是insert和delete大致的记录方式。1.6 日志刷盘的规则log buffer中未刷到磁盘的日志称为脏日志(dirty log)。在上面的说过，默认情况下事务每次提交的时候都会刷事务日志到磁盘中，这是因为变量 innodb_flush_log_at_trx_commit 的值为1。但是innodb不仅仅只会在有commit动作后才会刷日志到磁盘，这只是innodb存储引擎刷日志的规则之一。刷日志到磁盘有以下几种规则：1.发出commit动作时。已经说明过，commit发出后是否刷日志由变量 innodb_flush_log_at_trx_commit 控制。2.每秒刷一次。这个刷日志的频率由变量 innodb_flush_log_at_timeout 值决定，默认是1秒。要注意，这个刷日志频率和commit动作无关。3.当log buffer中已经使用的内存超过一半时。4.当有checkpoint时，checkpoint在一定程度上代表了刷到磁盘时日志所处的LSN位置。1.7 数据页刷盘的规则及checkpoint内存中(buffer pool)未刷到磁盘的数据称为脏数据(dirty data)。由于数据和日志都以页的形式存在，所以脏页表示脏数据和脏日志。上一节介绍了日志是何时刷到磁盘的，不仅仅是日志需要刷盘，脏数据页也一样需要刷盘。在innodb中，数据刷盘的规则只有一个：checkpoint。但是触发checkpoint的情况却有几种。*不管怎样，**checkpoint**触发后，会将buffer*中脏数据页和脏日志页都刷到磁盘。**innodb存储引擎中checkpoint分为两种：sharp checkpoint：在重用redo log文件(例如切换日志文件)的时候，将所有已记录到redo log中对应的脏数据刷到磁盘。fuzzy checkpoint：一次只刷一小部分的日志到磁盘，而非将所有脏日志刷盘。有以下几种情况会触发该检查点：master thread checkpoint：由master线程控制，每秒或每10秒刷入一定比例的脏页到磁盘。flush_lru_list checkpoint：从MySQL5.6开始可通过 innodb_page_cleaners 变量指定专门负责脏页刷盘的page cleaner线程的个数，该线程的目的是为了保证lru列表有可用的空闲页。async/sync flush checkpoint：同步刷盘还是异步刷盘。例如还有非常多的脏页没刷到磁盘(非常多是多少，有比例控制)，这时候会选择同步刷到磁盘，但这很少出现；如果脏页不是很多，可以选择异步刷到磁盘，如果脏页很少，可以暂时不刷脏页到磁盘dirty page too much checkpoint：脏页太多时强制触发检查点，目的是为了保证缓存有足够的空闲空间。too much的比例由变量 innodb_max_dirty_pages_pct 控制，MySQL 5.6默认的值为75，即当脏页占缓冲池的百分之75后，就强制刷一部分脏页到磁盘。由于刷脏页需要一定的时间来完成，所以记录检查点的位置是在每次刷盘结束之后才在redo log中标记的。MySQL停止时是否将脏数据和脏日志刷入磁盘，由变量innodb_fast_shutdown={ 0|1|2 }控制，默认值为1，即停止时只做一部分purge，忽略大多数flush操作(但至少会刷日志)，在下次启动的时候再flush剩余的内容，实现fast shutdown。1.8 LSN超详细分析LSN称为日志的逻辑序列号(log sequence number)，在innodb存储引擎中，lsn占用8个字节。LSN的值会随着日志的写入而逐渐增大。根据LSN，可以获取到几个有用的信息：1.数据页的版本信息。2.写入的日志总量，通过LSN开始号码和结束号码可以计算出写入的日志量。3.可知道检查点的位置。实际上还可以获得很多隐式的信息。LSN不仅存在于redo log中，还存在于数据页中，在每个数据页的头部，有一个fil_page_lsn记录了当前页最终的LSN值是多少。通过数据页中的LSN值和redo log中的LSN值比较，如果页中的LSN值小于redo log中LSN值，则表示数据丢失了一部分，这时候可以通过redo log的记录来恢复到redo log中记录的LSN值时的状态。redo log的lsn信息可以通过 show engine innodb status 来查看。MySQL 5.5版本的show结果中只有3条记录，没有pages flushed up to。mysql> show engine innodb stauts --- LOG --- Log sequence number 2225502463 Log flushed up to 2225502463 Pages flushed up to 2225502463 Last checkpoint at 2225502463 0 pending log writes, 0 pending chkp writes 3201299 log i/o's done, 0.00 log i/o's/second其中：log sequence number就是当前的redo log(in buffer)中的lsn；log flushed up to是刷到redo log file on disk中的lsn；pages flushed up to是已经刷到磁盘数据页上的LSN；last checkpoint at是上一次检查点所在位置的LSN。innodb从执行修改语句开始：(1).首先修改内存中的数据页，并在数据页中记录LSN，暂且称之为data_in_buffer_lsn；(2).并且在修改数据页的同时(几乎是同时)向redo log in buffer中写入redo log，并记录下对应的LSN，暂且称之为redo_log_in_buffer_lsn；(3).写完buffer中的日志后，当触发了日志刷盘的几种规则时，会向redo log file on disk刷入重做日志，并在该文件中记下对应的LSN，暂且称之为redo_log_on_disk_lsn；(4).数据页不可能永远只停留在内存中，在某些情况下，会触发checkpoint来将内存中的脏页(数据脏页和日志脏页)刷到磁盘，所以会在本次checkpoint脏页刷盘结束时，在redo log中记录checkpoint的LSN位置，暂且称之为checkpoint_lsn。(5).要记录checkpoint所在位置很快，只需简单的设置一个标志即可，但是刷数据页并不一定很快，例如这一次checkpoint要刷入的数据页非常多。也就是说要刷入所有的数据页需要一定的时间来完成，中途刷入的每个数据页都会记下当前页所在的LSN，暂且称之为data_page_on_disk_lsn。详细说明如下图：上图中，从上到下的横线分别代表：时间轴、buffer中数据页中记录的LSN(data_in_buffer_lsn)、磁盘中数据页中记录的LSN(data_page_on_disk_lsn)、buffer中重做日志记录的LSN(redo_log_in_buffer_lsn)、磁盘中重做日志文件中记录的LSN(redo_log_on_disk_lsn)以及检查点记录的LSN(checkpoint_lsn)。假设在最初时(12:0:00)所有的日志页和数据页都完成了刷盘，也记录好了检查点的LSN，这时它们的LSN都是完全一致的。假设此时开启了一个事务，并立刻执行了一个update操作，执行完成后，buffer中的数据页和redo log都记录好了更新后的LSN值，假设为110。这时候如果执行 show engine innodb status 查看各LSN的值，即图中①处的位置状态，结果会是：log sequence number(110) > log flushed up to(100) = pages flushed up to = last checkpoint at之后又执行了一个delete语句，LSN增长到150。等到12:00:01时，触发redo log刷盘的规则(其中有一个规则是 innodb_flush_log_at_timeout 控制的默认日志刷盘频率为1秒)，这时redo log file on disk中的LSN会更新到和redo log in buffer的LSN一样，所以都等于150，这时 show engine innodb status ，即图中②的位置，结果将会是：log sequence number(150) = log flushed up to > pages flushed up to(100) = last checkpoint at再之后，执行了一个update语句，缓存中的LSN将增长到300，即图中③的位置。假设随后检查点出现，即图中④的位置，正如前面所说，检查点会触发数据页和日志页刷盘，但需要一定的时间来完成，所以在数据页刷盘还未完成时，检查点的LSN还是上一次检查点的LSN，但此时磁盘上数据页和日志页的LSN已经增长了，即：log sequence number > log flushed up to 和 pages flushed up to > last checkpoint at但是log flushed up to和pages flushed up to的大小无法确定，因为日志刷盘可能快于数据刷盘，也可能等于，还可能是慢于。但是checkpoint机制有保护数据刷盘速度是慢于日志刷盘的：当数据刷盘速度超过日志刷盘时，将会暂时停止数据刷盘，等待日志刷盘进度超过数据刷盘。等到数据页和日志页刷盘完毕，即到了位置⑤的时候，所有的LSN都等于300。随着时间的推移到了12:00:02，即图中位置⑥，又触发了日志刷盘的规则，但此时buffer中的日志LSN和磁盘中的日志LSN是一致的，所以不执行日志刷盘，即此时 show engine innodb status 时各种lsn都相等。随后执行了一个insert语句，假设buffer中的LSN增长到了800，即图中位置⑦。此时各种LSN的大小和位置①时一样。随后执行了提交动作，即位置⑧。默认情况下，提交动作会触发日志刷盘，但不会触发数据刷盘，所以 show engine innodb status 的结果是：log sequence number = log flushed up to > pages flushed up to = last checkpoint at最后随着时间的推移，检查点再次出现，即图中位置⑨。但是这次检查点不会触发日志刷盘，因为日志的LSN在检查点出现之前已经同步了。假设这次数据刷盘速度极快，快到一瞬间内完成而无法捕捉到状态的变化，这时 show engine innodb status 的结果将是各种LSN相等。1.9 innodb的恢复行为在启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如二进制日志)要快很多。而且，innodb自身也做了一定程度的优化，让恢复速度变得更快。重启innodb时，checkpoint表示已经完整刷到磁盘上data page上的LSN，因此恢复时仅需要恢复从checkpoint开始的日志部分。例如，当数据库在上一次checkpoint的LSN为10000时宕机，且事务是已经提交过的状态。启动数据库时会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从检查点开始恢复。还有一种情况，在宕机前正处于checkpoint的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度。这时候一宕机，数据页中记录的LSN就会大于日志页中的LSN，在重启的恢复过程中会检查到这一情况，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。另外，事务日志具有幂等性，所以多次操作得到同一结果的行为在日志中只记录一次。而二进制日志不具有幂等性，多次操作会全部记录下来，在恢复的时候会多次执行二进制日志中的记录，速度就慢得多。例如，某记录中id初始值为2，通过update将值设置为了3，后来又设置成了2，在事务日志中记录的将是无变化的页，根本无需恢复；而二进制会记录下两次update操作，恢复时也将执行这两次update操作，速度比事务日志恢复更慢。1.10 和redo log有关的几个变量innodb_flush_log_at_trx_commit={0|1|2} # 指定何时将事务日志刷到磁盘，默认为1。innodb_log_buffer_size：# log buffer的大小，默认8Minnodb_log_file_size：#事务日志的大小，默认5Minnodb_log_files_group =2：# 事务日志组中的事务日志文件个数，默认2个innodb_log_group_home_dir =./：# 事务日志组路径，当前目录表示数据目录innodb_mirrored_log_groups =1：# 指定事务日志组的镜像组个数，但镜像功能好像是强制关闭的，所以只有一个log group。在MySQL5.7中该变量已经移除。0表示每秒将”log buffer”同步到”os buffer”且从”os buffer”刷到磁盘日志文件中。1表示每事务提交都将”log buffer”同步到”os buffer”且从”os buffer”刷到磁盘日志文件中。2表示每事务提交都将”log buffer”同步到”os buffer”但每秒才从”os buffer”刷到磁盘日志文件中。2.undo log2.1 基本概念undo log有两个作用：提供回滚和多个行版本控制(MVCC)。在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。另外，undo log也会产生redo log，因为undo log也要实现持久性保护。**2.2 undo log的存储方式innodb存储引擎对undo的管理采用段的方式。rollback segment称为回滚段，每个回滚段中有1024个undo log segment。**在以前老版本，只支持1个rollback segment，这样就只能记录1024个undo log segment。后来MySQL5.5可以支持128个rollback segment，即支持128*1024个undo操作，还可以通过变量 innodb_undo_logs (5.6版本以前该变量是 innodb_rollback_segments )自定义多少个rollback segment，默认值为128。undo log默认存放在共享表空间中。[root@xuexi data]# ll /mydata/data/ib* -rw-rw---- 1 mysql mysql 79691776 Mar 31 01:42 /mydata/data/ibdata1 -rw-rw---- 1 mysql mysql 50331648 Mar 31 01:42 /mydata/data/ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Mar 31 01:42 /mydata/data/ib_logfile1如果开启了 innodb_file_per_table ，将放在每个表的.ibd文件中。在MySQL5.6中，undo的存放位置还可以通过变量 innodb_undo_directory 来自定义存放目录，默认值为”.“表示datadir。默认rollback segment全部写在一个文件中，但可以通过设置变量 innodb_undo_tablespaces 平均分配到多少个文件中。该变量默认值为0，即全部写入一个表空间文件。该变量为静态变量，只能在数据库示例停止状态下修改，如写入配置文件或启动时带上对应参数。但是innodb存储引擎在启动过程中提示，不建议修改为非0的值，如下：2017-03-31 13:16:00 7f665bfab720 InnoDB: Expected to open 3 undo tablespaces but was able 2017-03-31 13:16:00 7f665bfab720 InnoDB: to find only 0 undo tablespaces. 2017-03-31 13:16:00 7f665bfab720 InnoDB: Set the innodb_undo_tablespaces parameter to the 2017-03-31 13:16:00 7f665bfab720 InnoDB: correct value and retry. Suggested value is 02.3 和undo log相关的变量undo相关的变量在MySQL5.6中已经变得很少。如下：它们的意义在上文中已经解释了。mysql> show variables like "%undo%"; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_undo_directory | . | | innodb_undo_logs | 128 | | innodb_undo_tablespaces | 0 | +-------------------------+-------+2.4 delete/update操作的内部机制当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。通过undo log记录delete和update操作的结果发现：(insert操作无需分析，就是插入行而已)delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。update分为两种情况：update的列是否是主键列。如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。如果是主键列，update分两部执行：先删除该行，再插入一行目标行。3.binlog和事务日志的先后顺序及group commit提醒：建议看看下面的评论。为了提高性能，通常会将有关联性的多个数据修改操作放在一个事务中，这样可以避免对每个修改操作都执行完整的持久化操作。这种方式，可以看作是人为的组提交(group commit)。除了将多个操作组合在一个事务中，记录binlog的操作也可以按组的思想进行优化：将多个事务涉及到的binlog一次性flush，而不是每次flush一个binlog。事务在提交的时候不仅会记录事务日志，还会记录二进制日志，但是它们谁先记录呢？二进制日志是MySQL的上层日志，先于存储引擎的事务日志被写入。在MySQL5.6以前，当事务提交(即发出commit指令)后，MySQL接收到该信号进入commit prepare阶段；进入prepare阶段后，立即写内存中的二进制日志，写完内存中的二进制日志后就相当于确定了commit操作；然后开始写内存中的事务日志；最后将二进制日志和事务日志刷盘，它们如何刷盘，分别由变量 sync_binlog 和 innodb_flush_log_at_trx_commit 控制。但因为要保证二进制日志和事务日志的一致性，在提交后的prepare阶段会启用一个prepare_commit_mutex锁来保证它们的顺序性和一致性。但这样会导致开启二进制日志后group commmit失效，特别是在主从复制结构中，几乎都会开启二进制日志。在MySQL5.6中进行了改进。提交事务时，在存储引擎层的上一层结构中会将事务按序放入一个队列，队列中的第一个事务称为leader，其他事务称为follower，leader控制着follower的行为。虽然顺序还是一样先刷二进制，再刷事务日志，但是机制完全改变了：删除了原来的prepare_commit_mutex行为，也能保证即使开启了二进制日志，group commit也是有效的。MySQL5.6中分为3个步骤：flush阶段、sync阶段、commit阶段。flush阶段：向内存中写入每个事务的二进制日志。sync阶段：将内存中的二进制日志刷盘。若队列中有多个事务，那么仅一次fsync操作就完成了二进制日志的刷盘操作。这在MySQL5.6中称为BLGC(binary log group commit)。commit阶段：leader根据顺序调用存储引擎层事务的提交，由于innodb本就支持group commit，所以解决了因为锁 prepare_commit_mutex 而导致的group commit失效问题。在flush阶段写入二进制日志到内存中，但是不是写完就进入sync阶段的，而是要等待一定的时间，多积累几个事务的binlog一起进入sync阶段，等待时间由变量 binlog_max_flush_queue_time 决定，默认值为0表示不等待直接进入sync，设置该变量为一个大于0的值的好处是group中的事务多了，性能会好一些，但是这样会导致事务的响应时间变慢，所以建议不要修改该变量的值，除非事务量非常多并且不断的在写入和更新。进入到sync阶段，会将binlog从内存中刷入到磁盘，刷入的数量和单独的二进制日志刷盘一样，由变量 sync_binlog 控制。当有一组事务在进行commit阶段时，其他新事务可以进行flush阶段，它们本就不会相互阻塞，所以group commit会不断生效。当然，group commit的性能和队列中的事务数量有关，如果每次队列中只有1个事务，那么group commit和单独的commit没什么区别，当队列中事务越来越多时，即提交事务越多越快时，group commit的效果越明显。
MySQL 地基基础：事务和锁的面纱什么是事务，为什么需要事务在 MySQL 中，事务是由一条或多条 SQL 组成的单位，在这个单位中所有的 SQL 共存亡，有点有福同享，有难同当的意思。要么全部成功，事务顺利完成；要么只要有一个 SQL 失败就会导致整个事务失败，所有已经做过的操作回退到原始数据状态。用日常细说事务的特性首先我们先说一下事务的四个特性：ACID。A：原子性（atomicity），一个事务要么全都成功，要么全都失败C：一致性（consistency），在事务的整个生命周期里，查询的数据是一致的，保证数据库不会返回未提交的事务的数据I：隔离性（isolation），一个事务所做的操作，在最终提交前，对其他事务是不可见的，保证事务与事务之间不会冲突D：持久性（durability），只要事务提交，数据就不会丢失，即使系统崩溃，事务也已经完成在日常生活中有很多的事情就能体现为数据库中的事务。比如“转账”，下面我们就具体展开，你就可以很清晰的认识事务的四个特性。时间：2020 年 1 月 1 日地点：某银行 ATM人物：A 和 B起因：B 向 A 借 1000 元人民币经过：A 转账给 B结果：转账成功或失败这么一个转账我们想一下底层基本的技术支撑与实现，B 向 A 借钱，A 要转账给 B，首先 A 必须有大于 1000 元的余额，然后从 A 的账户减 1000 元，在 B 的账户里加 1000 元。我们定义一个事务：获取 A 账户余额 select balance from account where username='A'; 在 A 账户里减 1000 元 update account set balance=balance-1000 where username='A'; 获取 B 账户余额 select balance from account where username='B'; 在 B 账户里加 1000 元 update account set balance=balance+1000 where username='B';好了，一个简单事务基本就这样，我们开始分析分析这个事务是如何保证事务的 ACID 的。原子性：这个事务要么全成功，要么全失败。事务成功则 1000 元转账到了 B 账户，事务失败回滚则 1000 元还在 A 账户里，就是说 1000 元不能凭空消失。一致性：在这个事务中，所有的查询都是一致的，我们先查询 A 账户余额是否大于 1000，如果小于 1000，事务失败回滚；如果获取不到 B 账户余额，事务失败回滚。隔离性：在这个事务发生的同时，发生了另一个事务（A 通过手机银行将钱全部转移到另外的账户，比如一共有 1500 元），第一个事务转 1000 元，第二个事务转 1500 元，我们仔细想想，如果都成功，那岂不是凭空获取了 1000 元，这是不合理的，每个事务在执行前都应查一下余额是否够本次转账的。这两个事务应该是隔离的，不能有冲突。持久性：转账成功了（即事务完成），这代表钱已经发生了转移，这个时候发生 ATM 吞卡、ATM 断电、手机银行无法登陆等等一切故障，反正钱已经转走了，钱没有丢（即数据没有丢）MySQL 并发控制技术并发控制技术可以说是数据库的底层基础技术，并发控制技术可以拆分来看，一是并发，一是控制。并发也就是说大量请求连接到数据库，控制就是数据库要控制好这些连接，保证资源的可用性、安全性，解决资源的挣用的问题。那么如何实现并控制呢？主要通过两个方面：LockMVCC先分别简单说一下 Lock 和 MVCC，具体的后面再聊。Lock，并发连接到数据库，操作有读和读、读和写、写和写，锁来保证并发连接使得数据可以保持一致性。MVCC（Multiversion Concurrency Control），多版本并发控制，是数据库的多版本，可以提高并发过程中的读和写操作，有效的避免写请求阻塞读请求。面试再也不怕被问到的 MVCC前面我们已经大致了解了 MVCC 是什么，以及他做什么事情，现在我们具体看看 MVCC 是如何工作的？我们知道数据的一致性，可以通过锁来保证，在并发连接中，锁机制在读和读的并发请求中不会锁数据，但是在读和写的并发请求中，写请求会加锁，读请求会被写请求阻塞，基于此，MVCC 发挥其作用。MVCC 控制两类操作：快照读：读取的是历史可见版本的数据，无锁当前读：读取的是当前最新版本的数据，加锁我们举个例子说一下吧，比如:mysql> create table tab1(id decimal,name varchar(10),address varchar(10),status decimal,primary key(id)); mysql> insert into tab1 values(1,'a','beijing',1);表中数据为：现在有一个请求，将数据 a 的地址改为 shanghai，这个数据更新的过程，我们细化一下，将历史数据置为失效，将新的数据插入：mysql> update tab1 set status=0 where name='a'; mysql> insert into tab1 value(2,'a','shanghai',1);表中数据为：MVCC 的原理就类似是这样的，address='beijing' 就是历史数据，更新前保存了下来，address='shanghai' 就是当前数据，新插入数据，这样并发连接来了，既可以读取历史数据，也可以修改当前数据。比如，现在有三个事务：T1 -> 要执行 update addressT2 -> 要执行 update addressT3 -> 要执行 update addressT1 先获取了表中这一行数据，执行了 update，未提交；T2 获取表中这一行数据，由于 T1 未提交，address=‘beijing’,这个 beijing 就来源历史数据；T3 也获取表中这一行数据，由于 T1 未提交，address='beijing'，这个 beijing 也来源历史数据。这样是不是好理解了。以此类推，如果只对 name='a' 这一行数据有 N 个并发连接要做 M 个操作，这些历史数据都保存在表中，这个表的数据量无法预估，势必会造成压力与瓶颈。多版本数据到底如何保存，这就不是本节考虑的问题了，是数据库 undo 帮你做的工作。这里就不展开了。（后期可能会做 undo 相关的 chat，大家可以关注我）简单易懂的实例帮你理解 MySQL 事务隔离级别事务隔离级别，拆分来看，事务、隔离、级别，故是三个概念的集合，是保证事务之间相互隔离互不影响的，有多个级别。事务在执行过程中可能会出现脏读、不可重复读、幻读，那么 MySQL 的事务隔离级别到底有怎样的表现呢？那么到底什么是脏读、不可重复读、幻读呢？脏读：一个事务读取了另一个未提交事务操作的数据。不可重复读：一个事务重新读取前面读取过的数据时，发现该数据已经被修改了或者不见了，其实已被另一个已提交的事务操作了。解决了脏读的问题。幻读：一个事务，需要更新数据，于是重新提交了一个查询，返回符合查询条件行，发现这些行因为其他提交的事务发生了改变，这些数据像“幻影”一样出现了。解决了不可重复读。接下来我们用具体实例分析各个事务隔离级别。创建测试表 t_account：mysql> create table t_account(name varchar(10),balance decimal); mysql> insert into t_account values('A',100); mysql> insert into t_account values('B',0);读未提交设置事务隔离级别：mysql> set global tx_isolation='read-uncommitted';查询事务隔离级别：mysql> SELECT @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+ 1 row in set (0.00 sec)当前事务可以读取另一个未提交事务操作的数据。环境：用户 A 有 100 元钱，给用户 A 增加 100 元，然后用户 A 转账给用户 B。现在我们查询一下用户 A 和用户 B 的余额：mysql> select * from t_account; +------+---------+ | name | balance | +------+---------+ | A | -100 | | B | 200 | +------+---------+ 2 rows in set (0.00 sec)问题来了，这个结果不符合预期，用户 A 竟然是 -100 元，用户 B 增加了 200 元，这是因为事务 2 读取了事务 1 未提交的数据。读提交设置事务隔离级别：mysql> set global tx_isolation='read-committed';查询事务隔离级别：mysql> SELECT @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-COMMITTED | +------------------+ 1 row in set (0.00 sec)当前事务只能读取另一个提交事务操作的数据。环境：用户 A 有 100 元钱，给用户 A 增加 100 元。一个事务重新读取前面读取过的数据时，发现该数据已经被修改了，其实已被另一个已提交的事务操作了。可重复读设置事务隔离级别：mysql> set global tx_isolation='repeatable-read';查询事务隔离级别：mysql> SELECT @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | REPEATABLE-READ | +------------------+ 1 row in set (0.00 sec)当前事务读取通过第一次读取建立的快照是一致的，即使另外一个事务提交了该数据。除非自己这个事务可以读取在自身事务中修改的数据。可重复读隔离级别是 MySQL 的默认隔离级别。环境：用户 A 有 100 元钱，给用户 A 增加 100 元。这就能看出来，事务 2 开启后读取了用户 A 的余额，即使事务 1 修改了数据，不管提交与否，事务 2 读取的数据一直是之前第一次读取的数据。继续操作。为什么现在变成了 200 元了，因为事务 2 已经 commit，再次 select 是一个新的事务，读取数据当然又变为第一次获取数据（此时的数据是最新的数据）。思考一下：上述这个举例是可重复读的 select 相关验证，如果是 DML 操作，会不会是同样的结果呢？思考三分钟……答案是：其他事物即使查询不到的数据，DML 操作也可能会影响那些提交的数据。好，让我验证一下。update 操作：delete 操作：通过这两个例子你是不是了解了一个事务的 update 和 delete 操作了另外一个事务提交的数据，会使得这些数据在当前事务变得可见。就像幻影一下出现了！序列化设置事务隔离级别：mysql> set global tx_isolation='serializable';查询事务隔离级别：mysql> SELECT @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | SERIALIZABLE | +------------------+ 1 row in set (0.00 sec)当前事务 select 和 DML 操作的数据都会加行锁，其他事务访问同样的数据需要等锁释放。环境：用户 A 有 100 元钱，给用户 A 增加 100 元。好了，实例讲解到此结束，是否已经帮你理解了 MySQL 事务隔离级别。另外，结合前面说的 MVCC，Read-Committed 和 Repeatable-Read，支持 MVCC；Read-Uncommitted 由于可以读取未提交的数据，不支持 MVCC；Serializable 会对所有读取的数据加行锁，不支持 MVCC。MySQL 锁机制（机智）锁是可以协调并发连接访问 MySQL 数据库资源的一种技术，可以保证数据的一致性。锁有两个阶段：加锁和解锁，InnoDB 引擎的锁主要有两类。共享锁（S）允许一个事务读取数据，阻塞其他事务想要获取相同数据。共享锁之间不互斥，读和读操作可以并行。代码展示：select * from table where ... lock in share mode排它锁（X）持有排他锁的事务可以更新数据，阻塞其他事务获取数据的排他锁和共享锁。排它锁之间互斥，读和写、写和写操作不可以并行。代码展示：select * from table where ... for update;从 MySQL 数据库的内外区分锁，有两种锁。内部锁MySQL 在数据库内部自动管理，协调并发连接的资源争用。内部锁再具体来看分为：行锁：会话事务将访问的行数据加锁表锁：会话事务将访问的表整体加锁外部锁会话层使用特殊的手段显示获取锁，阻塞其他会话对数据的操作。我们通过外部操作命令实现外部锁，比如使用 lock table 和 unlock tables。我们举个例子来描述一下这个过程吧，比如有事务 1 和事务 2，事务 1 锁定了一行数据，加了一个 S 锁；事务 2 想要对整个表加锁，需要判断这个表是否被加了表锁，表中的每一行是否有行锁。仔细想想这个过程是很快呢？还是非常的慢？如果表很小无所谓了，如果表是海量级数据，那糟了，事务 2 势必耗费很多资源。如何解决事务 2 这种检索资源消耗的问题呢？事务意向锁帮你先获取意向，先一步问问情况，然后再获取我们想要的 S 和 X 锁，具体分为：意向共享锁（IS）事务 1 说：我要加一个行锁，我有这个意向，你们其他人有没有意见，如果没有我就先拿这个 IS 锁了。意向排它锁（IX）事务 2 说：我要加一个表锁，这个可是排他锁，我拿了你们就等我用完再说吧，我有这个意向，你们其他人有没有意见，如果没有我就先拿这个 IX 锁了。前面这个举例，其过程升级优化为：事务 1 先申请获取 IS 锁，成功后，获取 S 锁事务 2 发现表中有 IS 锁了，事务 2 获取表锁会被阻塞那么这四个锁之间兼容性如何呢？聊几个经典死锁案例在实际应用中经常发生数据库死锁的情况，那么什么是死锁呢？说白了就是事务 1 锁事务 2，事务 2 锁事务 1，这两个事务都在等着对方释放锁资源，陷入了死循环。接下来我们介绍几个经典死锁案例，MySQL 默认级别使用的是 REPEATABLE-READ。场景 1：insert 死锁创建一个测试表：mysql> create table t_insert(id decimal,no decimal,primary key(id),unique key(no));session1：mysql> begin; mysql> insert into t_insert values(1,101);session2：mysql> begin; mysql> insert into t_insert values(2,101);此时会话一直等待无响应。session1：mysql> insert into t_insert values(3,100);结果如下。此时 session2 立马报出来死锁：ERROR 1213 (40001): ==Deadlock== found when trying to get lock; try restarting transaction数据库中 insert 作为最简单的 SQL，为什么会导致死锁呢？session1 在插入(1,101) 的时候会加一个 X 锁；session2 插入(2,101)，no 字段有着唯一性，故 session2 在插入时数据库会做 duplicate 冲突检测，由于事务冲突先加 S 锁；然后 session1 又插入了 (3,100)，此时 session1 会加 insert intention X 锁（插入意向锁），之前 session1 已经有了 X 锁，故进入等待队列，结局就是 session1 和 session2 都在等待，陷入了僵局，MySQL 很机智，牺牲一方事务解决这个尴尬的局面，所以 session2 被干掉了，报错死锁。场景 2：自增列死锁自增列死锁问题和场景 1 的类似，比如将场景 1 的主键属性改为自增长属性，主键自增仍唯一，场景模拟类似，加锁的过程也类似，产生死锁的过程也类似，这里就不详细模拟了。场景 3：rollback 死锁创建一个测试表：mysql> create table t_rollback(id decimal,no decimal,primary key(id),unique key(no));session1：mysql> begin; mysql> insert into t_rollback values(1,100);session2：mysql> begin; mysql> insert into t_rollback values(2,100);此时会话一直等待无响应。session3mysql> begin; mysql> insert into t_rollback values(3,100);此时会话一直等待无响应。session1mysql> rollback;结果如下： 此时 session1 执行了 rollback 成功返回，session2 的 insert 返回成功，session3 立马报出来死锁。ERROR 1213 (40001): ==Deadlock== found when trying to get lock; try restarting transaction为什么我回滚了事务，还要报死锁，难道我需要全部回滚吗？session1 在插入 (1,100) 的时候会加一个 X 锁；session2 插入 (2,100)，no 字段有着唯一性，故 session2 在插入时数据库会做 duplicate 冲突检测，由于事务冲突先加 S 锁；session3 插入 (3,100)，no 字段有着唯一性，故 session3 在插入时数据库会做 duplicate 冲突检测，由于事务冲突先加 S 锁；session1 回滚，session2 申请 insert intention X 锁，等 session3;session3 申请 insert intention X 锁，等 session2，结局就是 session2 和 session3 都在等待，陷入了僵局，MySQL 很机智，牺牲一方事务解决这个尴尬的局面，所以 session3 被干掉了，报错死锁。场景 4：commit 死锁创建一个测试表：mysql> create table t_commit(id decimal,no decimal,primary key(id),unique key(no)); mysql> insert into t_commit values(1,100);session1：mysql> begin; mysql> delete from t_commit where id=1;session2：mysql> begin; mysql> insert into t_commit values(1,100);此时会话一直等待无响应。session3：mysql> begin; mysql> insert into t_commit values(1,100);此时会话一直等待无响应。session1：mysql> commit;结果如下：此时 session1 执行了 commit 成功返回，session3 的 insert 返回成功，session2 立马报出来死锁。ERROR 1213 (40001): ==Deadlock== found when trying to get lock; try restarting transaction为什么我提交了事务，还要报死锁，难道我需要全部提交吗？这个产生死锁的过程和场景 3rollback 死锁类似，大家可以和之前的 rollback 死锁产生过程对应来看。小技巧——事务保存点帮你读档重闯关玩游戏你是不是有过存档、读档的经历，过某一个比较难的关卡，先存档，过不了，就读档重新过。数据库中我们也可以如此，MySQL 事务保存点可以回滚到事务的某时间点，并且不用中止事务。下面举例说明一下。用户 B 和用户 C 向用户 A 借钱，用户 A 转账给用户 B 和用户 C，转账的过程中发生了用户 C 账户不存在，那么我们也要把转给用户 B 的钱也取消吗？我们可以不取消，使用一个保存点即可。查询用户 A 有 1000 元：mysql> select balance from t_account where name='A';转账 100 元给用户 B：mysql> update t_account set balance=balance-100 where name='A'; mysql> update t_account set balance=balance+100 where name='B';设置事务保存点mysql> savepoint T_A_TO_B;转账 200 元给用户 C：mysql> update t_account set balance=balance-200 where name='A'; mysql> update t_account set balance=balance+200 where name='C'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0发现转账给 C 返回有 0 条受影响的行，转账给 C 未成功，此时用户 A 已经少了 200 元了，先退 200 元再排查吧，转账给用户 B 的不需要重新操作了。mysql> rollback to T_A_TO_B; mysql> commit；根据提示 0 条受影响的行，也就是说用户 C 不存在呀，我们查询一下个用户信息：mysql> select * from t_account where name='A'; +------+---------+ | name | balance | +------+---------+ | A | 900 | +------+---------+ 1 row in set (0.00 sec) mysql> select * from t_account where name='B'; +------+---------+ | name | balance | +------+---------+ | B | 200 | +------+---------+ 1 row in set (0.00 sec) mysql> select * from t_account where name='C'; Empty set (0.00 sec)结果：用户 A 成功转 100 元给用户 B，用户 C 果然不存才，设置了保存点，帮我们省了很多工作，中途不用取消全部操作。小技巧——一个死锁的具体分析方法前面我们学习了事务、锁，以及介绍了几个经典死锁案例，当遇到死锁，我们怎样具体分析呢？分析死锁，我们就需要看死锁的日志信息，通过日志具体找到死锁的原因及执行的语句。首先，我们用前面的场景 1 模拟一个死锁。然后，执行如下命令获取死锁信息：mysql> show engine innodb status;在打印的日志中，先看事务 1 的日志：*** (1) TRANSACTION: TRANSACTION 2179, ACTIVE 8 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 32, OS thread handle 140317789804288, query id 823 localhost root update insert into t_insert values(2,101) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 37 page no 4 n bits 72 index no of table `test`.`t_insert` trx id 2179 lock mode S waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 5; hex 8000000065; asc e;; 1: len 5; hex 8000000001; asc ;; TRANSACTION 2179, ACTIVE ==8 sec== inserting事务 1 持续了 8 秒：mysql ==tables in use 1==, locked 1 涉及一张表 LOCK WAIT 2 lock struct(s) 有两个锁 insert into t_insert values(2,101) 这是 SQL 语句 WAITING FOR THIS LOCK TO BE GRANTED 唯一行锁处于等待 RECORD LOCKS space id 37 page no 4 n bits 72 index no 加锁的是索引字段 no lock mode S waiting 锁等待为 S 锁事务 2 的日志：*** (2) TRANSACTION: TRANSACTION 2178, ACTIVE 17 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2 MySQL thread id 33, OS thread handle 140317663659776, query id 824 localhost root update insert into t_insert values(3,100) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 37 page no 4 n bits 72 index no of table `test`.`t_insert` trx id 2178 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 5; hex 8000000065; asc e;; 1: len 5; hex 8000000001; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 37 page no 4 n bits 72 index no of table `test`.`t_insert` trx id 2178 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 5; hex 8000000065; asc e;; 1: len 5; hex 8000000001; asc ;;HOLDS THE LOCK(S)持有锁的内容lock_mode X locks持有锁的锁等待内容是一个 x 锁WAITING FOR THIS LOCK TO BE GRANTED等待锁的内容lock_mode X locks gap before rec insert intention waiting等待锁的锁等待内容也是一个 x 锁通过这些日志，我们发现日志中的事务 1，持有 S 锁，S 锁的出现是因为需要检查数据唯一性，我们的 no 字段确实有唯一索引，这一点也正好验证了。日志中的事务 1，持有一个 X 锁，又等待一个 X 锁。所以场景 1 中的两个事务都在锁等，造成了死锁。小技巧——换种思路提高事务能力在数据中如果是单一事务，那没的说，一个一个的事务来执行，毫无压力。现实是不允许这样的，肯定是有大量的并发连接，并发事务在所难免。如果高并发的环境中，事务处理效率肯定大幅下降，这个时候我们有没有方法提高并发事务能力呢？我们解决技术处理问题的限制，这次我们换一种思路来提高事务能力。比如：合理的在线、离线数据库比如我们的系统数据量日益增加，还有一些业务需要查询大量的数据，我们可以改造系统为在线、离线数据库，在线表提供高效事务能力，离线表提供数据查询服务，互不影响。提高 delete 操作效率的思考如果你对表有大量数据的 delete 操作，比如定期的按日、月、年删除数据，可以设计表为日表、月表、年表亦或是相对应的分区表，这样清理数据会由大事务降低为小事务。MySQL 地基基础：数据字典数据字典是什么MySQL 数据字典的发展史：MySQL 4 提供了 information_schema 数据字典，可以简单的使用 SQL 来检索系统元数据。MySQL 5.5 提供了 performa nce_schema 性能引擎，可以查看 MySQL 性能问题，但是这个有一定难度。MySQL 5.7 提供了 sys 系统数据库，其包含的表、视图、函数、存储过程、触发器可以帮我们快速了解数据库的情况。数据字典的作用数据字典我们用一句话来概括，就是数据的数据，用于查询数据库中数据的信息内容。MySQL information_schema 详解（崭露头角)我们部署完 MySQL 后发现会自动生成一个 information_schema 库，这个库提供了访问 MySQL 元数据的访问方式。那么什么是元数据呢？元数据就是数据字典，就是数据的数据，前者这个数据就是我们所知道，所用的数据，后者这个数据就是解释前者的数据。比如，数据库名、表名、列名、列类型、权限等等。这个库中存在这大量的视图，我们只能查看其视图内容，不能修改。我们看看这个库中到底有什么内容。mysql> use sys mysql> select * from schema_object_overview where db='information_schema'; +--------------------+-------------+-------+ | db | object_type | count | +--------------------+-------------+-------+ | information_schema | SYSTEM VIEW | 61 | +--------------------+-------------+-------+ 1 row in set (0.10 sec)结果显示，有 61 张系统视图。既然是视图，那就没有相关的数据文件了，我们去验证一下。到 MySQL 的 data 目录中看看是否有 information_schema 相关的数据文件。经过查询根本找不到这个库的目录。都有什么视图内容呢？我们可以通过 show tables 命令查看。mysql> use information_schema mysql> show tables;接下来我们讲几个 information_schema 中重要常用视图。SCHEMATA：查看 MySQL 实例中所有数据库信息TABLES：查询数据库中的表、视图信息COLUMNS：查询表中的列信息STATISTICS：查询表中的索引信息USER_PRIVILEGES：查询用户权限信息SCHEMA_PRIVILEGES：查询数据库权限信息TABLE_PRIVILEGES：查询表权限信息COLUMN_PRIVILEGES：查询列权限信息CHARACTER_SETS：查询字符集信息好了，information_schema 不做过多的介绍了，查询的内容比较全面，也比较简单，大家可以自行去探索。MySQL performance_schema 详解（成长升级）MySQL 在 5.7 开始，对数据字典的使用有了很大的改进，使用上更加的方便，提供的能力也更高。它可以查询事务信息、获取元数据锁、跟踪事件、统计内存使用情况等等。我们先说一个你想不到的事情，MySQL 的 performance_schema 其实是一个引擎。mysql> select * from information_schema.engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)看到了吗，在 MySQL 支持的存储引擎中发现了 PERFORMANCE_SCHEMA，是不是很神奇。在 MySQL 配置中可以配置启用这个引擎，默认是启动的。在 my.cnf 中配置如下：[mysqld] performance_schema=ON验证一下参数是否启动：mysql> show variables like 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | ON | +--------------------+-------+ 1 row in set (0.01 sec)虽然它是一个引擎，但是我们可以像使用数据库那样使用 use 来使用它。这个库里到底有什么内容呢？mysql> use sys; mysql> select * from schema_object_overview where db='performance_schema'; +--------------------+-------------+-------+ | db | object_type | count | +--------------------+-------------+-------+ | performance_schema | BASE TABLE | 87 | +--------------------+-------------+-------+ 1 row in set (0.06 sec)结果显示，有 87 张表。我们知道 MySQL 有很多需要监控和统计的内容，而 performance_schema 将这些监控、统计信息之类的内容通过库中的表统计出来，都展现在这些表中。那么这些表都是做什么用的呢？我们就去研究一下。总体分类：setup 表instance 表wait event 表stage event 表statement event 表transaction event 表summary 表other 表setup 表mysql> use performance_schema mysql> show tables like '%setup%'; +----------------------------------------+ | Tables_in_performance_schema (%setup%) | +----------------------------------------+ | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | +----------------------------------------+ 5 rows in set (0.00 sec)setup_actors作用：配置用户维度的监控，默认监控所有用户。mysql> select * from setup_actors; +------+------+------+---------+---------+ | HOST | USER | ROLE | ENABLED | HISTORY | +------+------+------+---------+---------+ | % | % | % | YES | YES | +------+------+------+---------+---------+ 1 row in set (0.00 sec)% 表示默认是对所有的用户监控。setup_consumers作用：配置事件的消费者类型，管理将收集的监控内容保存在哪些表中。mysql> select * from setup_consumers; +----------------------------------+---------+ | NAME | ENABLED | +----------------------------------+---------+ | events_stages_current | NO | | events_stages_history | NO | | events_stages_history_long | NO | | events_statements_current | YES | | events_statements_history | YES | | events_statements_history_long | NO | | events_transactions_current | NO | | events_transactions_history | NO | | events_transactions_history_long | NO | | events_waits_current | NO | | events_waits_history | NO | | events_waits_history_long | NO | | global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +----------------------------------+---------+ 15 rows in set (0.00 sec)有 15 条记录，这些配置呢存在着上下级关系，原则是当上级监控生效，下级监控才起作用。上下级对应关系如下：global_instrumentation |----thread_instrumentation | |----events_waits_current | | |----events_waits_history | | |----events_waits_history_long | |----events_stages_current | | |----events_stages_history | | |----events_stages_history_long | |----events_statements_current | |----events_statements_history | |----events_statements_history_long |-----statements_digest第一级：global_instrumentation 是全局统计，只有它生效其余的才生效。如果设置它生效，其余都设置未生效，则只收集全局统计信息，不收集用户级统计信息。第二级：thread_instrumentation 是用户线程统计，statements_digest 是全局 SQL 统计。第三级：events_waits_current、events_stages_current、events_statements_current，分别是事件的 wait、stage、statement 的统计。第四级：分别是对应的历史统计内容了。setup_instruments作用：配置仪器，这个表中内容非常丰富，包含了统计 SQL 执行阶段情况、统计等待事件情况、IO 情况、内存情况、锁情况等。配置内容很多，我们分组看一下几大类。mysql> select name,count(*) from setup_instruments group by LEFT(name,5); +-------------------------------------------+----------+ | name | count(*) | +-------------------------------------------+----------+ | idle | 1 | | memory/performance_schema/mutex_instances | 377 | | stage/sql/After create | 129 | | statement/sql/select | 193 | | transaction | 1 | | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | 319 | +-------------------------------------------+----------+ 6 rows in set (0.00 sec)如果你执行上面的这个分组报错如下：ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'performance_schema.setup_instruments.NAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by解决方法：这是由于 sql_mode=only_full_group_by 导致。mysql> SELECT @@SESSION.sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@SESSION.sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> set @@session.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';通过修改会话级的 sql_mode 可以解决问题。idle：表示空闲memory：表示内存的统计stage：表示 SQL 语句每个阶段的统计statement：表示 SQL 语句的统计transaction：表示事务的统计wait：表示各种等待的统计setup_objects作用：配置监控对象。mysql> select * from setup_objects; +-------------+--------------------+-------------+---------+-------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED | +-------------+--------------------+-------------+---------+-------+ | EVENT | mysql | % | NO | NO | | EVENT | performance_schema | % | NO | NO | | EVENT | information_schema | % | NO | NO | | EVENT | % | % | YES | YES | | FUNCTION | mysql | % | NO | NO | | FUNCTION | performance_schema | % | NO | NO | | FUNCTION | information_schema | % | NO | NO | | FUNCTION | % | % | YES | YES | | PROCEDURE | mysql | % | NO | NO | | PROCEDURE | performance_schema | % | NO | NO | | PROCEDURE | information_schema | % | NO | NO | | PROCEDURE | % | % | YES | YES | | TABLE | mysql | % | NO | NO | | TABLE | performance_schema | % | NO | NO | | TABLE | information_schema | % | NO | NO | | TABLE | % | % | YES | YES | | TRIGGER | mysql | % | NO | NO | | TRIGGER | performance_schema | % | NO | NO | | TRIGGER | information_schema | % | NO | NO | | TRIGGER | % | % | YES | YES | +-------------+--------------------+-------------+---------+-------+ 20 rows in set (0.00 sec)通过结果我们看到默认对 EVENT、FUNCTION、PROCEDURE、TABLE、TRIGGER 的配置：mysql 库都不监控performance_schema 都不监控information_schema 都不监控其余库都监控setup_timers作用：配置每种类型统计的时间单位。mysql> select * from setup_timers; +-------------+-------------+ | NAME | TIMER_NAME | +-------------+-------------+ | idle | MICROSECOND | | wait | CYCLE | | stage | NANOSECOND | | statement | NANOSECOND | | transaction | NANOSECOND | +-------------+-------------+ 5 rows in set (0.00 sec)idle：微妙wait：CPU 时钟stage：纳秒statement：纳秒transaction：纳秒那么为什么使用这样的时间呢，这个时间定义来源于 MySQL 的基础定义。mysql> select * from performance_timers; +-------------+-----------------+------------------+----------------+ | TIMER_NAME | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD | +-------------+-----------------+------------------+----------------+ | CYCLE | 2193855721 | 1 | 18 | | NANOSECOND | 1000000000 | 1 | 152 | | MICROSECOND | 1000000 | 1 | 155 | | MILLISECOND | 1037 | 1 | 155 | | TICK | 108 | 1 | 561 | +-------------+-----------------+------------------+----------------+ 5 rows in set (0.00 sec)TICK：是系统的相对时间单位，也称为系统的时基，来源于定时器的周期性中断。instance 表mysql> use performance_schema mysql> show tables like '%instances'; +-------------------------------------------+ | Tables_in_performance_schema (%instances) | +-------------------------------------------+ | cond_instances | | file_instances | | mutex_instances | | prepared_statements_instances | | rwlock_instances | | socket_instances | +-------------------------------------------+ 6 rows in set (0.00 sec)cond_instances：保存系统中使用的条件变量的对象file_instances：保存系统中打开文件的对象mutex_instances：保存系统中使用互斥变量的对象prepared_statements_instances：保存预处理 SQL 语句的 statements 的对象rwlock_instances：保存系统中使用读写锁的对象socket_instances：保存系统中使用的 socket 的对象wait event 表mysql> use performance_schema mysql> show tables like '%waits%'; +-----------------------------------------------+ | Tables_in_performance_schema (%waits%) | +-----------------------------------------------+ | events_waits_current | | events_waits_history | | events_waits_history_long | | events_waits_summary_by_account_by_event_name | | events_waits_summary_by_host_by_event_name | | events_waits_summary_by_instance | | events_waits_summary_by_thread_by_event_name | | events_waits_summary_by_user_by_event_name | | events_waits_summary_global_by_event_name | | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | +-----------------------------------------------+ 12 rows in set (0.00 sec)这里是说前三个，其他的后面介绍。events_waits_current：保存当前线程的等待事件events_waits_history：保存每个线程的最近 N 个等待事件events_waits_history_long：保存所有线程的最近 N 个等待事件stage event 表mysql> use performance_schema mysql> show tables like '%stage%'; +------------------------------------------------+ | Tables_in_performance_schema (%stage%) | +------------------------------------------------+ | events_stages_current | | events_stages_history | | events_stages_history_long | | events_stages_summary_by_account_by_event_name | | events_stages_summary_by_host_by_event_name | | events_stages_summary_by_thread_by_event_name | | events_stages_summary_by_user_by_event_name | | events_stages_summary_global_by_event_name | +------------------------------------------------+ 8 rows in set (0.00 sec)这里是说前三个，其他的后面介绍。events_stages_current：保存当前线程所处的执行阶段events_stages_history：保存当前线程最新的 N 个执行阶段events_stages_history_long：保存当前线程最新的 N 个执行阶段statement event 表mysql> use performance_schema mysql> show tables like '%statement%'; +----------------------------------------------------+ | Tables_in_performance_schema (%statement%) | +----------------------------------------------------+ | events_statements_current | | events_statements_history | | events_statements_history_long | | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_program | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | | prepared_statements_instances | +----------------------------------------------------+ 11 rows in set (0.00 sec)这里是说前三个，其他的后面介绍。events_statements_current：保存当前线程执行的语句events_statements_history：保存每个线程最新的 N 个执行的语句events_statements_history_long：保存每个线程最新的 N 个执行的语句transaction event 表mysql> use performance_schema mysql> show tables like '%transactions%'; +------------------------------------------------------+ | Tables_in_performance_schema (%transactions%) | +------------------------------------------------------+ | events_transactions_current | | events_transactions_history | | events_transactions_history_long | | events_transactions_summary_by_account_by_event_name | | events_transactions_summary_by_host_by_event_name | | events_transactions_summary_by_thread_by_event_name | | events_transactions_summary_by_user_by_event_name | | events_transactions_summary_global_by_event_name | +------------------------------------------------------+ 8 rows in set (0.00 sec)这里是说前三个，其他的后面介绍。events_transactions_current：保存每个线程当前事务事件events_transactions_history：保存每个线程最近的 N 个事务事件events_transactions_history_long：保存每个线程最近的 N 个事务事件summary 表mysql> use performance_schema mysql> show tables like '%summary%'; +------------------------------------------------------+ | Tables_in_performance_schema (%summary%) | +------------------------------------------------------+ | events_stages_summary_by_account_by_event_name | | events_stages_summary_by_host_by_event_name | | events_stages_summary_by_thread_by_event_name | | events_stages_summary_by_user_by_event_name | | events_stages_summary_global_by_event_name | | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_program | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | | events_transactions_summary_by_account_by_event_name | | events_transactions_summary_by_host_by_event_name | | events_transactions_summary_by_thread_by_event_name | | events_transactions_summary_by_user_by_event_name | | events_transactions_summary_global_by_event_name | | events_waits_summary_by_account_by_event_name | | events_waits_summary_by_host_by_event_name | | events_waits_summary_by_instance | | events_waits_summary_by_thread_by_event_name | | events_waits_summary_by_user_by_event_name | | events_waits_summary_global_by_event_name | | file_summary_by_event_name | | file_summary_by_instance | | memory_summary_by_account_by_event_name | | memory_summary_by_host_by_event_name | | memory_summary_by_thread_by_event_name | | memory_summary_by_user_by_event_name | | memory_summary_global_by_event_name | | objects_summary_global_by_type | | socket_summary_by_event_name | | socket_summary_by_instance | | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | +------------------------------------------------------+ 36 rows in set (0.00 sec)这些 summary 表有很多，提供了一段时间内已经执行完成的事件的汇总情况，我们从不同的维度整理如下：按阶段事件的汇总摘要：events_stages*按语句事件的汇总摘要：events_statements*按事务事件的汇总摘要：events_transactions*按等待事件的汇总摘要：events_waits*按文件事件的汇总摘要：file*按内存事件的汇总摘要：memory*按对象事件的汇总摘要：objects_summary_global_by_type按套接字事件的汇总摘要：socket*按表事件的汇总摘要：table*other 表其他的表还有很多，可以监控统计 accounts、file、status、hosts、memory、metadata_locks、replication、session、socket、table、threads 等。好了，performance_schema 是数据库，是性能引擎，内部逻辑比较复杂，能做的事情也很多，这里就先介绍到这里，大家可以继续深入研究。MySQL sys 详解（演变进化）MySQL 在 5.7 版本引入了 sys Schema，这个 sys 可以理解为是一个 MySQL 系统库，这个库中提供了表、视图、函数、存储过程、触发器，这些就可以帮我们快捷、高效地知道 MySQL 数据库的元数据信息，比如我们可以了解：SQL 执行情况是否使用了索引，是否走了全表扫描，统计信息的情况、内存使用情况、IO 使用情况、会话连接等等。前面我们学习了 information_schema 和 performance_schema，这个 sys 提供的视图其实就是前面这两个化繁为简的总结，降低复杂度，让你更快乐的了解 MySQL 的现状。可见 MySQL 在自我优化方面是多么的努力，它帮你做了很多的工作，我们可以更简单的获取更直观的数据，怎么样，MySQL 优秀吧。说这么多了，这个 sys 库里到底有什么内容呢？好，赶紧一睹芳容。mysql> use sys mysql> select * from schema_object_overview where db='sys'; +-----+---------------+-------+ | db | object_type | count | +-----+---------------+-------+ | sys | FUNCTION | 22 | | sys | PROCEDURE | 26 | | sys | VIEW | 100 | | sys | BASE TABLE | 1 | | sys | INDEX (BTREE) | 1 | | sys | TRIGGER | 2 | +-----+---------------+-------+ 6 rows in set (0.01 sec)结果显示：这些内容可以帮我们做什么呢？视图：获取更可读的 performance_schema 中的数据存储过程：调整 performance_schema 的配置信息，生成系统诊断报告等函数：查询 performance_schema 配置信息，提供格式化数据等1 张表在这些所有内容中，我们常用的就是这一张表和其他视图，我们先来看看这唯一一张表，它是 sys_config。mysql> select * from sys_config; +--------------------------------------+-------+---------------------+--------+ | variable | value | set_time | set_by | +--------------------------------------+-------+---------------------+--------+ | diagnostics.allow_i_s_tables | OFF | 2020-12-16 19:14:32 | NULL | | diagnostics.include_raw | OFF | 2020-12-16 19:14:32 | NULL | | ps_thread_trx_info.max_length | 65535 | 2020-12-16 19:14:32 | NULL | | statement_performance_analyzer.limit | 100 | 2020-12-16 19:14:32 | NULL | | statement_performance_analyzer.view | NULL | 2020-12-16 19:14:32 | NULL | | statement_truncate_len | 64 | 2020-12-16 19:14:32 | NULL | +--------------------------------------+-------+---------------------+--------+ 6 rows in set (0.00 sec)只有简单的 6 行数据，这张表保存的是基础参数的配置内容。内容级别是会话级。默认最后一列 set_by（配置修改者）为空，其保存的内容是最后一次修改配置时的用户名。参数说明diagnostics.allow_i_s_tables：默认 OFF，这参数控制调用 diagnostics() 存储过程时会扫描 information_schema.tables 找到所有的基表与 statistics 表关联查询，扫描每个表的统计信息。diagnostics.include_raw：默认 OFF，这参数控制调用 diagnostics() 存储过程输出包含 metrics 视图的原始信息。ps_thread_trx_info.max_length：默认 65535，保存的是 ps_thread_trx_info() 函数生成的 json 输出内容的最大长度。statement_performance_analyzer.limit：默认 100，返回不具有内置限制的视图的行数。statement_performance_analyzer.view：默认 NULL，给 statement_performance_analyzer() 存储过程当作入参使用的自定义查询或视图名称。statement_truncate_len：默认 64，控制 format_statement() 函数返回的语句的最大长度。接下来我们测试修改一下 statement_truncate\len 这个参数内容：# statement_truncate_len，调用 format_statement()函数返回是 64 字节长度的值，在未被调用过任何涉及到该配置选项的函数之前，该参数的值是 NULL。 mysql> select @sys.statement_truncate_len; +----------------------------------------------------------+ | @sys.statement_truncate_len | +----------------------------------------------------------+ | NULL | +----------------------------------------------------------+ 1 row in set (0.00 sec) # 调用一下 format_statement()函数 mysql> set @stmt='select variable,value,set_time,set_by from sys_config'; mysql> select format_statement(@stmt); +----------------------------------------------------------+ | format_statement(@stmt) | +----------------------------------------------------------+ | select variable,value,set_time,set_by from sys_config | +----------------------------------------------------------+ 1 row in set (0.00 sec) 此时结果可以正常显示 SQL。 # 调用过 format_statement()函数之后，参数的值会更新为 64 mysql> select @sys.statement_truncate_len; +-----------------------------+ | @sys.statement_truncate_len | +-----------------------------+ | 64 | +-----------------------------+ 1 row in set (0.00 sec) 此时看到 statement_truncate_len 值内容为 64 了 #修改一下 statement_truncate_len 的值为 32 mysql> set @sys.statement_truncate_len=32; mysql> select @sys.statement_truncate_len; +-----------------------------+ | @sys.statement_truncate_len | +-----------------------------+ | 32 | +-----------------------------+ 1 row in set (0.00 sec) # 再次调用 format_statement()函数，可以看到返回的结果内容显示不全了，因为我们把 statement_truncate_len 改为了 32 导致。 mysql> select format_statement(@stmt); +-----------------------------------+ | format_statement(@stmt) | +-----------------------------------+ | select variabl ... rom sys_config | +-----------------------------------+ 1 row in set (0.00 sec)上面这 6 行配置时默认自带的，sys_config 中还有一个 sys.debug 参数，这个参数默认没有，我们可以手工插入。debug默认是 NULL，调用 diagnostics() 和 execute_prepared_stmt() 存储过程，执行检查。这个参数默认不存在，是临时使用的。# 会话级设置 set @sys.debug = NULL; # 所有会话使用，需要插入到表中 mysql> insert into sys_config (variable, value) values('debug', 'ON'); mysql> select * from sys_config; +--------------------------------------+-------+---------------------+--------+ | variable | value | set_time | set_by | +--------------------------------------+-------+---------------------+--------+ | debug | ON | 2021-02-07 15:53:12 | NULL | | diagnostics.allow_i_s_tables | OFF | 2020-12-16 19:14:32 | NULL | | diagnostics.include_raw | OFF | 2020-12-16 19:14:32 | NULL | | ps_thread_trx_info.max_length | 65535 | 2020-12-16 19:14:32 | NULL | | statement_performance_analyzer.limit | 100 | 2020-12-16 19:14:32 | NULL | | statement_performance_analyzer.view | NULL | 2020-12-16 19:14:32 | NULL | | statement_truncate_len | 64 | 2020-12-16 19:14:32 | NULL | +--------------------------------------+-------+---------------------+--------+ 7 rows in set (0.00 sec) # 更新 mysql> update sys_config set value = 'OFF' where variable = 'debug';2 个触发器前面 sys_config 这个表介绍的差不多了，接下来我们说一下这两个触发器，他们和这张表有紧密的关系。在 MySQL 5.7 开始提供了一个新的用户 mysql.sys，这个用户可避免修改或删除 root 用户时发生的问题，但是该用户被锁定是无法连接客户端的。接下来说的两个触发器，在定义时使用了 DEFINER=mysql.sys@localhost，就是说只能用 mysql.sys 调用触发器，从而对表 sys_config 的内容做修改，如果 mysql.sys 用户不存在会报错ERROR 1449 (HY000): The user specified as a definer ('mysql.sys'@'localhost') does not exist假如，我是说假如 mysql.sys 用户被你给误删除了，或者其他原因导致这个用户不存在了，我们如何补救呢？（建议：千万不要去动这个用户，以免造成不必要的麻烦）# 首先创建用户，并赋予使用触发器权限 mysql> grant TRIGGER on sys.* to 'mysql.sys'@'localhost' identified by '123456'; mysql> INSERT INTO sys.sys_config (variable, value) VALUES('debug', 'ON'); ERROR 1143 (42000): SELECT command denied to user 'mysql.sys'@'localhost' for column 'set_by' in table 'sys_config' # 还需要赋予 select、insert、update 权限 mysql> grant select,insert,update on sys.sys_config to 'mysql.sys'@'localhost'; mysql> INSERT INTO sys.sys_config (variable, value) VALUES('debug', 'ON'); Query OK, 1 row affected (0.02 sec) mysql> UPDATE sys.sys_config SET value = 'OFF' WHERE variable = 'debug'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0sysconfiginsertsetuser当对 sys.sys_config 表做 insert 操作时，该触发器会将 sys_config 表的 set_by 列设置为当前用户名。sysconfigupdatesetuser当对 sys.sys_config 表做 insert 操作时，该触发器会将 sys_config 表的 set_by 列设置为当前用户名。这两个触发器可以更新 set_by 字段都有一个前提条件：mysql> set @sys.ignore_sys_config_triggers=0;100 张视图在 MySQL 的 sys 库中有 100 个视图，其中有 52 个是字母的，有 48 个是 x$开头的，有什么区别呢？前者是格式化的数据，更加适合人类阅读；后者是数据库原始数据，适合工具采集数据使用。我们重点介绍一下字母开头的视图，重点分为几类：host_summary：服务器层级，以 IP 分组，汇总 IO 信息。innodb：InnoDB 层级，汇总 innodb 存储引擎信息和事务锁、等待等信息。io：IO 层级，汇总 IO 使用情况、IO 等待情况等。memory：内存使用情况。metrics：数据库内部统计值。processlist：线程情况。ps_check_lost_instrumentation：发生监控丢失的信息情况。schema：模式层级，汇总表统计信息等。session：会话层级，汇总会话情况。statement：执行语句层级，汇总统计信息等。user_summary：用户层级，以用户分组，汇总用户使用文件 IO 信息，执行语句的统计信息等。wait：汇总主机，等待事情等。接下来我们重点介绍几个视图。host_summary：这个视图我们可以查看连接数据库的主机情况，统计每个主机 SQL 执行次数、SQL 执行时长、表扫描次数、文件 IO 情况、连接情况、用户情况、内存分布情况。通过这些信息我们可以快速了解连接数据库的主机情况。hostsummarybyfileio_type：查询连接数据库每个主机的文件 IO 使用情况。hostsummarybyfileio：查询连接数据库主机的总 IO 使用情况。innodbbufferstatsbyschema：扫描整个 buffer pool 来统计查看每个库的内存占用情况。如果生产环境 buffer pool 很大，扫描会占用很多资源，造成性能问题，慎用。innodbbufferstatsbytable：扫描整个 buffer pool 来统计查看每个库的每个对象的内存占用情况。如果生产环境 buffer pool 很大，扫描会占用很多资源，造成性能问题，慎用。ioglobalbyfileby_bytes：查询数据库的 IO 情况。memorybyhostbycurrent_bytes：查询连接数据库的主机内存情况。memorybythreadbycurrent_bytes：查询连接数据库的线程内存情况。memorybyuserbycurrent_bytes：查询连接数据库的用户内存情况。processlist：查询数据库连接情况。session：查询连接数据库的会话情况。schematablelock_waits：查询锁等待情况。schematablestatistics：查询对表的 insert、update、delete、select 的 IO 情况。schematableswithfulltable_scans：查询全表扫描情况。schemaautoincrement_columns：查询自增列情况。schemaobjectoverview：查询 MySQL 中每个数据库的对象情况（包括表、索引、视图、函数、存储过程、触发器）。schemaredundantindexes：查询数据库的冗余索引情况。schemaunusedindexes：查询数据库中没有使用索引的情况。schemaindexstatistics：查询索引的 select、insert、update、delete 情况。statement_analysis：查询执行语句总体的统计信息情况。statementswitherrorsorwarnings：查询执行语句的错误和警告情况。statementswithfulltablescans：查询全表扫描情况。statementswithruntimesin95th_percentile：查询语句平均执行时间大于整体 95%平均分布的情况。statementswithsorting：查询使用了文件排序的情况。statementswithtemp_tables：查询使用了临时表的执行语句情况。user_summary：查询连接的总体执行时间、平均执行时间、IO、内存等情况。version：查询 sys schema 和 MySQL 版本情况。waitclassesglobalbyavg_latency：查询等待事件的平均延迟时间情况。waitclassesglobalbylatency：查询等待事件的总体延迟时间情况。以上差不多就是 sys 库常用的视图了，基本满足我们的日常分析统计需求，大家可以通过官网继续深入学习，学好这一部分的内容，对 MySQL 的底层原理及性能分析有非常大的帮助。MySQL 地基基础：数据库字符集字符集简介字符是各种文字和符号的总称，而字符集是多个字符的集合。字符集分类与特点字符包含有国家文字、标点符号、图形符号、数字等内容，字符集是多个字符的集合，字符集种类非常多，每种字符集包含的字符个数是不相同的。比如说，国家文字不同，就会使用各自国家通用字符集，这样是不是好理解一些。常见字符集分类ASCII（American Standard Code for Information Interchange，美国信息互换标准编码）：是基于罗马字母表的一套电脑编码系统，一个字节表示一个字符。LATIN1：ASCII 字符集的扩充，仍然使用一个字节表示一个字符。GB2312（信息交换用汉字编码字符集·基本集）：中国国家标准的简体中文字符集，基本满足了汉字的计算机处理需要，分区表示，双字节表示一个字符。GB18030（信息交换用汉字编码字符集基本集的扩充）：是 GB2312 的扩充，更全面，兼容 Unicode 3.0 和 GB2312。UTF-8（Unicode Tranformation Format）：是 Unicode 的其中一个使用方式，支持所有国家字符集，使用 1-4 个字节表示一个字符。字符集有很多，这里不一一列举。想要获取全部支持字符集，可以在库中查看，详见下文。字符集特点不同的编码方式，最终解释为不同的机器语言（二进制）不同的表示方式，使用 1 个或者多个字节表示一个字符MySQL 与字符集之间亲密关系首先我们想一下，数据库是做什么的？数据库是存放数据的环境。这些数据库是如何保存的？拍拍脑袋一想，肯定不是原封不动的存放的，数据库不同于我们的大脑，它的思维能力甚至不如阿猫阿狗，它只能读懂 0 和 1，那么数据库存储的数据，其实就是一串串的 0 和 1 的组合，那么按照什么规则定义这些 0 和 1 呢？重点来了，数据库需要在字符集中找到对应的数据编码，然后存储的是这些编码。MySQL 字符集包括字符集（character）和校对规则（collation），字符集用来定义存储字符串的方式，校队规则定义比较字符串的方式。字符集和校对规则是一对多的关系，每个字符集至少对应一个校对规则。后面我们在具体说说校对规则。MySQL 在底层存储上有四个级别的字符集设置，分别是：服务器级、数据库级、表级、列级。字符集优先级是：列字符集 > 表字符集 > 数据库字符集 > 服务器字符集，其中最常用的是表级和数据库级字符集。列级字符集最优先，但是一般不单独设置，使其继承表级字符集。获取所有数据库字符集：mysql> show character set; 或者 mysql> select * from information_schema.character_sets;获取所有数据库校对规则：mysql> show collation; 或者 mysql> select * from information_schema.collations;字符集编码原理在客户端和服务器端之期间的数据请求与反馈，会经历一系列的转换，从而保证数据的正确无误无乱码，下面具体说一下这个请求的过程。\1. MySQL client 发送请求（字符集为 character_set_client） character_set_client 就是客户端字符集。\2. MySQL Server 收到请求，将请求数据从 character_set_client 转换为 character_set_connection 通常情况下 character_set_connection 字符集同 character_set_client 字符集一致。\3. MySQL Server 将请求数据从 character_set_connection 转换为内部操作字符集。何为内部操作字符集，其实就是前面说的列字符集、表字符集、数据库字符集、服务器字符集这四个。这一步操作会使用每个数据字段的 CHARACTER SET 设定值：如果 CHARACTER SET 不存在，使用表字符集如果表字符集不存在，使用数据库字符集如果数据库字符集不存在，使用服务器字符集\4. MySQL Server 将操作结果从内部操作字符集转换为 character_set_results character_set_results 就是结果内容的字符集。字符集与校对规则分析前面我们已经说了说字符集和校对规则的关系。接下来我们具体分析分析它们。查看数据库字符集：mysql> show variables like 'character%';参数解释：character_set_client：客户端使用的字符集character_set_connection：客户端和服务端连接层字符集character_set_database：默认数据库字符集 ，如没有默认数据库，会用 character_set_server 指定的字符集，建议由系统自动管理character_set_filesystem：把 os 上文件名转化成此字符集，把 character_set_client 转换 character_set_filesystem， 默认 binary 是不做任何转换character_set_results：结果字符集character_set_server：数据库服务器字符集character_set_system：操作系统字符集，无需设置，总是 utf8DDL 字符集选择：建库操作，未指定数据库字符集，继承数据库服务器字符集建表操作，未指定表字符集，继承当前库字符集新增字段，未指定列字符集，继承表字符集修改字段，未指定列字符集，继承表字符集DML 字符集选择：插入、更新数据，由 character_set_client 转换为 character_set_connection 再转换为表字符集查看字符集校对规则：mysql> show variables like 'collation%';参数说明：collation_connection：当前连接的字符集校对规则collation_database：当前数据库默认校对规则collation_server：当前数据库服务器默认校对规则字符集和校对规则：每个字符集至少有一个校对规则每个字符集有一个默认的校对规则每个校对规则只能属于一个字符集校对规则命名字符集名称_语言_后缀，其中后缀有三种写法：_ci：不区分大小写_cs：区分大小写_bin：二进制如何更改 MySQL 字符集更改数据库字符集：alter database db1 default character set utf8;更改数据库表的字符集：alter table tab1 default character set utf8;把表默认的字符集和所有字符列改为新的字符集（例如 utf8）：alter table tab1 convert to character set utf8;前面的操作转换了字符集之间的列类型。如果有一列使用一种字符集（如 latin1），但是存储的值实际上使用了其它的字符集（如 utf8），这种情况不是你想要的，进行如下操作就可以解决你的问题。alter table tab1 change 字段 1 字段 2 类型； alter table tab1 change 字段 2 字段 2 类型 character set utf8;字符集最佳实践MySQL 数据库字符集和校对规则有 4 个级别：服务器级、数据库级、表级、字段级，下面我们重点讲解及用实例操作来实践。服务器级设置字符集方式一在 MySQL 配置文件 my.cnf 中进行配置设置：[mysqld] default-character-set=gbk方式二在启动 MySQL 时设置：mysqld --default-character-set=gbk方式三在源码编译时指定，如果未指定，默认使用 latin1：./configure --with-charcter=gbk数据库级设置字符集创建数据库时指定字符集（create database … ）修改数据库时修改字符集（alter database …）注：对于已经存在的数据修改字符集无效。表级设置字符集创建表时指定字符集（create table …）修改表时修改字符集（alter table …）注：对于已经存在的数据修改字符集无效。列级设置字符集创建表时指定列字符集（create table …）修改表时修改列字符集（alter table …）注：对于已经存在的数据修改字符集无效。细心的朋友应该已经注意到了，以上这种修改是不适用于已经存在的数据的，如果表中已经存在数据了，那么我们怎样修改字符集呢？不要着急，后面会一步一步的教你如何实现。首先我们先实例说一下如何设置字符集。1. 为数据库设置字符集和校对规则设置数据库字符集：create database db1 default character set utf8;设置数据库校对规则：create database db1 default character set utf8 collate utf8_bin;说明：如果指定了字符集和校对规则，则使用指定的如果指定了字符集未指定校对规则，则使用指定字符集和默认校对规则如果未指定字符集和校对规则，则使用服务器字符集和校对规则2. 为表设置字符集和校对规则设置表字符集：create table tab1(column1 varchar(5)) default character set utf8;设置表校对规则：create table tab1(column1 varchar(5)) default character set utf8 collate utf8_bin;说明：如果指定了字符集和校对规则，则使用指定的如果指定了字符集未指定校对规则，则使用指定字符集和默认校对规则如果未指定字符集和校对规则，则使用数据库字符集和校对规则3. 为列设置字符集和校对规则设置列字符集：create table tab1(column1 varchar(5) character SET utf8);设置列校对规则：create table tab1(column1 varchar(5) character set utf8 collate utf8_bin);说明：如果指定了字符集和校对规则，则使用指定的如果指定了字符集未指定校对规则，则使用指定字符集和默认校对规则如果未指定字符集和校对规则，则使用表字符集和校对规则4. 如何处理带数据的字符集当表中已经存在数据，直接更改字符集，不会更改既有的数据字符集，我们需要先将数据导出，调整字符集再导入。第一步：导出表结构mysqldump -uroot -p --default-character-set=gbk -d db1> createtab.sql第二步：修改表字符集编辑修改 createtab.sql 文件，将表结构定义中的字符集改为新的字符集。第三步：导出所有数据mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 db1> data.sql第四步：修改数据字符集编辑修改 data.sql，将 set names latin1 修改成 set names gbk。第五步：创建数据库create database db1 default charset gbk;第六步：创建表mysql -uroot -p db1 < createtab.sql第七步：导入数据mysql -uroot -p db1 < data.sql
MySQL 主从复制本文非常详细地介绍MySQL复制相关的内容，包括基本概念、复制原理、如何配置不同类型的复制(传统复制)等等。在此文章之后，还有几篇文章分别介绍GTID复制、半同步复制、实现MySQL的动静分离，以及MySQL 5.7.17引入的革命性功能：组复制(MGR)。本文是MySQL Replication的基础，但却非常重要。对于MySQL复制，如何搭建它不是重点(因为简单，网上资源非常多)，如何维护它才是重点(网上资源不集中)。以下几个知识点是掌握MySQL复制所必备的：复制的原理将master上已存在的数据恢复到slave上作为基准数据获取正确的binlog坐标深入理解show slave status中的一些状态信息本文对以上内容都做了非常详细的说明。希望对各位初学、深入MySQL复制有所帮助。mysql replication官方手册：1.复制的基本概念和原理mysql复制是指从一个mysql服务器(MASTER)将数据通过日志的方式经过网络传送到另一台或多台mysql服务器(SLAVE)，然后在slave上重放(replay或redo)传送过来的日志，以达到和master数据同步的目的。它的工作原理很简单。首先确保master数据库上开启了二进制日志，这是复制的前提。在slave准备开始复制时，首先要执行change master to语句设置连接到master服务器的连接参数，在执行该语句的时候要提供一些信息，包括如何连接和要从哪复制binlog，这些信息在连接的时候会记录到slave的datadir下的master.info文件中，以后再连接master的时候将不用再提供这新信息而是直接读取该文件进行连接。在slave上有两种线程，在slave准备开始复制时，首先要执行change master to语句设置连接到master服务器的连接参数，在执行该语句的时候要提供一些信息，包括如何连接和要从哪复制binlog，这些信息在连接的时候会记录到slave的datadir下的master.info文件中，以后再连接master的时候将不用再提供这新信息而是直接读取该文件进行连接。在slave上有两种线程，分别是IO线程和SQL线程IO线程用于连接master，监控和接受master的binlog。当启动IO线程成功连接master时，master会同时启动一个dump线程，该线程将slave请求要复制的binlog给dump出来，之后IO线程负责监控并接收master上dump出来的二进制日志，当master上binlog有变化的时候，IO线程就将其复制过来并写入到自己的中继日志(relay log)文件中。slave上的另一个线程SQL线程用于监控、读取并重放relay log中的日志，将数据写入到自己的数据库中。如下图所示。站在slave的角度上看，过程如下：站在master的角度上看，过程如下(默认的异步复制模式，前提是设置了sync_binlog=1，否则binlog刷盘时间由操作系统决定)：所以，可以认为复制大致有三个步骤：数据修改写入master数据库的binlog中。slave的IO线程复制这些变动的binlog到自己的relay log中。slave的SQL线程读取并重新应用relay log到自己的数据库上，让其和master数据库保持一致。从复制的机制上可以知道，在复制进行前，slave上必须具有master上部分完整内容作为复制基准数据。例如，master上有数据库A，二进制日志已经写到了pos1位置，那么在复制进行前，slave上必须要有数据库A，且如果要从pos1位置开始复制的话，还必须有和master上pos1之前完全一致的数据。如果不满足这样的一致性条件，那么在replay中继日志的时候将不知道如何进行应用而导致数据混乱。也就是说，复制是基于binlog的position进行的，复制之前必须保证position一致。(注：这是传统的复制方式所要求的)可以选择对哪些数据库甚至数据库中的哪些表进行复制。默认情况下，MySQL的复制是异步的。slave可以不用一直连着master，即使中间断开了也能从断开的position处继续进行复制。MySQL 5.6对比MySQL 5.5在复制上进行了很大的改进，主要包括支持GTID(Global Transaction ID,全局事务ID)复制和多SQL线程并行重放。GTID的复制方式和传统的复制方式不一样，通过全局事务ID，它不要求复制前slave有基准数据，也不要求binlog的position一致。MySQL 5.7.17则提出了组复制(MySQL Group Replication,MGR)的概念。像数据库这样的产品，必须要尽可能完美地设计一致性问题，特别是在集群、分布式环境下。Galera就是一个MySQL集群产品，它支持多主模型(多个master)，但是当MySQL 5.7.17引入了MGR功能后，Galera的优势不再明显，甚至MGR可以取而代之。MGR为MySQL集群中多主复制的很多问题提供了很好的方案，可谓是一项革命性的功能。复制和二进制日志息息相关，所以学习本章必须先有二进制日志的相关知识。2.复制的好处围绕下面的拓扑图来分析：主要有以下几点好处：1.提供了读写分离的能力。replication让所有的slave都和master保持数据一致，因此外界客户端可以从各个slave中读取数据，而写数据则从master上操作。也就是实现了读写分离。需要注意的是，为了保证数据一致性，写操作必须在master上进行。通常说到读写分离这个词，立刻就能意识到它会分散压力、提高性能。2.为MySQL服务器提供了良好的伸缩(scale-out)能力。由于各个slave服务器上只提供数据检索而没有写操作，因此”随意地”增加slave服务器数量来提升整个MySQL群的性能，而不会对当前业务产生任何影响。之所以”随意地”要加上双引号，是因为每个slave都要和master建立连接，传输数据。如果slave数量巨多，master的压力就会增大，网络带宽的压力也会增大。3.数据库备份时，对业务影响降到最低。由于MySQL服务器群中所有数据都是一致的(至少几乎是一致的)，所以在需要备份数据库的时候可以任意停止某一台slave的复制功能(甚至停止整个mysql服务)，然后从这台主机上进行备份，这样几乎不会影响整个业务(除非只有一台slave，但既然只有一台slave，说明业务压力并不大，短期内将这个压力分配给master也不会有什么影响)。4.能提升数据的安全性。这是显然的，任意一台mysql服务器断开，都不会丢失数据。即使是master宕机，也只是丢失了那部分还没有传送的数据(异步复制时才会丢失这部分数据)。5.数据分析不再影响业务。需要进行数据分析的时候，直接划分一台或多台slave出来专门用于数据分析。这样OLTP和OLAP可以共存，且几乎不会影响业务处理性能。3.复制分类和它们的特性MySQL支持两种不同的复制方法：传统的复制方式和GTID复制。MySQL 5.7.17之后还支持组复制(MGR)。(1).传统的复制方法要求复制之前，slave上必须有基准数据，且binlog的position一致。(2).GTID复制方法不要求基准数据和binlog的position一致性。GTID复制时，master上只要一提交，就会立即应用到slave上。这极大地简化了复制的复杂性，且更好地保证master上和各slave上的数据一致性。从数据同步方式的角度考虑，MySQL支持4种不同的同步方式：同步(synchronous)、半同步(semisynchronous)、异步(asynchronous)、延迟(delayed)。所以对于复制来说，就分为同步复制、半同步复制、异步复制和延迟复制。3.1 同步复制客户端发送DDL/DML语句给master，master执行完毕后还需要等待所有的slave都写完了relay log才认为此次DDL/DML成功，然后才会返回成功信息给客户端。同步复制的问题是master必须等待，所以延迟较大，在MySQL中不使用这种复制方式。例如上图中描述的，只有3个slave全都写完relay log并返回ACK给master后，master才会判断此次DDL/DML成功。3.2 半同步复制客户端发送DDL/DML语句给master，master执行完毕后还要等待一个slave写完relay log并返回确认信息给master，master才认为此次DDL/DML语句是成功的，然后才会发送成功信息给客户端。半同步复制只需等待一个slave的回应，且等待的超时时间可以设置，超时后会自动降级为异步复制，所以在局域网内(网络延迟很小)使用半同步复制是可行的。例如上图中，只有第一个slave返回成功，master就判断此次DDL/DML成功，其他的slave无论复制进行到哪一个阶段都无关紧要。3.3 异步复制客户端发送DDL/DML语句给master，master执行完毕立即返回成功信息给客户端，而不管slave是否已经开始复制。这样的复制方式导致的问题是，当master写完了binlog，而slave还没有开始复制或者复制还没完成时，slave上和master上的数据暂时不一致，且此时master突然宕机，slave将会丢失一部分数据。如果此时把slave提升为新的master，那么整个数据库就永久丢失这部分数据。3.4 延迟复制顾名思义，延迟复制就是故意让slave延迟一段时间再从master上进行复制。4.配置一主一从此处先配置默认的异步复制模式。由于复制和binlog息息相关，如果对binlog还不熟悉，请先了解binlog，见：。mysql支持一主一从和一主多从。但是每个slave必须只能是一个master的从，否则从多个master接受二进制日志后重放将会导致数据混乱的问题。在开始传统的复制(非GTID复制)前，需要完成以下几个关键点，这几个关键点指导后续复制的所有步骤。为master和slave设定不同的server-id，这是主从复制结构中非常关键的标识号。到了MySQL 5.7，似乎不设置server id就无法开启binlog。设置server id需要重启MySQL实例。开启master的binlog。刚安装并初始化的MySQL默认未开启binlog，建议手动设置binlog且为其设定文件名，否则默认以主机名为基名时修改主机名后会找不到日志文件。最好设置master上的变量sync_binlog=1(MySQL 5.7.7之后默认为1，之前的版本默认为0)，这样每写一次二进制日志都将其刷新到磁盘，让slave服务器可以尽快地复制。防止万一master的二进制日志还在缓存中就宕机时，slave无法复制这部分丢失的数据。最好设置master上的redo log的刷盘变量innodb_flush_log_at_trx_commit=1(默认值为1)，这样每次提交事务都会立即将事务刷盘保证持久性和一致性。在slave上开启中继日志relay log。这个是默认开启的，同样建议手动设置其文件名。建议在master上专门创建一个用于复制的用户，它只需要有复制权限replication slave用来读取binlog。确保slave上的数据和master上的数据在”复制的起始position之前”是完全一致的。如果master和slave上数据不一致，复制会失败。记下master开始复制前binlog的position，因为在slave连接master时需要指定从master的哪个position开始复制。考虑是否将slave设置为只读，也就是开启read_only选项。这种情况下，除了具有super权限(mysql 5.7.16还提供了super_read_only禁止super的写操作)和SQL线程能写数据库，其他用户都不能进行写操作。这种禁写对于slave来说，绝大多数场景都非常适合。4.1 一主一从一主一从是最简单的主从复制结构。本节实验环境如下：配置master和slave的配置文件。[mysqld] # master datadir=/data socket=/data/mysql.sock log-bin=master-bin sync-binlog=1 server-id=100 [mysqld] # slave datadir=/data socket=/data/mysql.sock relay-log=slave-bin server-id=111重启master和slave上的MySQL实例。service mysqld restart在master上创建复制专用的用户。create user 'repl'@'192.168.100.%' identified by 'P@ssword1!'; grant REPLICATION SLAVE on *.* to 'repl'@'192.168.100.%';将slave恢复到master上指定的坐标。这是备份恢复的内容，此处用一个小节来简述操作过程。详细内容见。4.2 将slave恢复到master指定的坐标对于复制而言，有几种情况：(1).待复制的master没有新增数据，例如新安装的mysql实例。这种情况下，可以跳过恢复这个过程。(2).待复制的master上已有数据。这时需要将这些已有数据也应用到slave上，并获取master上binlog当前的坐标。只有slave和master的数据能匹配上，slave重放relay log时才不会出错。第一种情况此处不赘述。第二种情况有几种方法，例如使用mysqldump、冷备份、xtrabackup等工具，这其中又需要考虑是MyISAM表还是InnoDB表。在实验开始之前，首先在master上新增一些测试数据，以innodb和myisam的数值辅助表为例。DROP DATABASE IF EXISTS backuptest; CREATE DATABASE backuptest; USE backuptest; # 创建myisam类型的数值辅助表和插入数据的存储过程 CREATE TABLE num_isam (n INT NOT NULL PRIMARY KEY) ENGINE = MYISAM ; DROP PROCEDURE IF EXISTS proc_num1; DELIMITER $$ CREATE PROCEDURE proc_num1 (num INT) BEGIN DECLARE rn INT DEFAULT 1 ; TRUNCATE TABLE backuptest.num_isam ; INSERT INTO backuptest.num_isam VALUES(1) ; dd: WHILE rn * 2 < num DO BEGIN INSERT INTO backuptest.num_isam SELECT rn + n FROM backuptest.num_isam; SET rn = rn * 2 ; END ; END WHILE dd; INSERT INTO backuptest.num_isam SELECT n + rn FROM backuptest.num_isam WHERE n + rn <= num; END ; $$ DELIMITER ; # 创建innodb类型的数值辅助表和插入数据的存储过程 CREATE TABLE num_innodb (n INT NOT NULL PRIMARY KEY) ENGINE = INNODB ; DROP PROCEDURE IF EXISTS proc_num2; DELIMITER $$ CREATE PROCEDURE proc_num2 (num INT) BEGIN DECLARE rn INT DEFAULT 1 ; TRUNCATE TABLE backuptest.num_innodb ; INSERT INTO backuptest.num_innodb VALUES(1) ; dd: WHILE rn * 2 < num DO BEGIN INSERT INTO backuptest.num_innodb SELECT rn + n FROM backuptest.num_innodb; SET rn = rn * 2 ; END ; END WHILE dd; INSERT INTO backuptest.num_innodb SELECT n + rn FROM backuptest.num_innodb WHERE n + rn <= num ; END ; $$ DELIMITER ; # 分别向两个数值辅助表中插入100W条数据 CALL proc_num1 (1000000) ; CALL proc_num2 (1000000) ;所谓数值辅助表是只有一列的表，且这个字段的值全是数值，从1开始增长。例如上面的是从1到100W的数值辅助表。mysql> select * from backuptest.num_isam limit 10; +----+ | n | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +----+4.2.1 获取master binlog的坐标如果master是全新的数据库实例，或者在此之前没有开启过binlog，那么它的坐标位置是position=4。之所以是4而非0，是因为binlog的前4个记录单元是每个binlog文件的头部信息。如果master已有数据，或者说master以前就开启了binlog并写过数据库，那么需要手动获取position。为了安全以及没有后续写操作，必须先锁表。mysql> flush tables with read lock;注意，这次的锁表会导致写阻塞以及innodb的commit操作。然后查看binlog的坐标。mysql> show master status; # 为了排版，简化了输出结果 +-------------------+----------+--------------+--------+--------+ | File | Position | Binlog_Do_DB | ...... | ...... | +-------------------+----------+--------------+--------+--------+ | master-bin.000001 | 623 | | | | +-------------------+----------+--------------+--------+--------+记住master-bin.000001和623。4.2.2 备份master数据到slave上下面给出3种备份方式以及对应slave的恢复方法。建议备份所有库到slave上，如果要筛选一部分数据库或表进行复制，应该在slave上筛选(筛选方式见后文)，而不应该在master的备份过程中指定。方式一：冷备份直接cp。这种情况只适用于没有新写入操作。严谨一点，只适合拷贝完成前master不能有写入操作。如果要复制所有库，那么直接拷贝整个datadir。如果要复制的是某个或某几个库，直接拷贝相关目录即可。但注意，这种冷备份的方式只适合MyISAM表和开启了innodb_file_per_table=ON的InnoDB表。如果没有开启该变量，innodb表使用公共表空间，无法直接冷备份。如果要冷备份innodb表，最安全的方法是先关闭master上的mysql，而不是通过表锁。所以，如果没有涉及到innodb表，那么在锁表之后，可以直接冷拷贝。最后释放锁。mysql> flush tables with read lock; mysql> show master status; # 为了排版，简化了输出结果 +-------------------+----------+--------------+--------+--------+ | File | Position | Binlog_Do_DB | ...... | ...... | +-------------------+----------+--------------+--------+--------+ | master-bin.000001 | 623 | | | | +-------------------+----------+--------------+--------+--------+ shell> rsync -avz /data 192.168.100.150:/ mysql> unlock tables;此处实验，假设要备份的是整个实例，因为涉及到了innodb表，所以建议关闭MySQL。因为是冷备份，所以slave上也应该关闭MySQL。# master和slave上都执行 shell> mysqladmin -uroot -p shutdown然后将整个datadir拷贝到slave上(当然，有些文件是不用拷贝的，比如master上的binlog、mysql库等)。# 将master的datadir(/data)拷贝到slave的datadir(/data) shell> rsync -avz /data 192.168.100.150:/需要注意，在冷备份的时候，需要将备份到目标主机上的DATADIR/auto.conf删除，这个文件中记录的是mysql server的UUID，而master和slave的UUID必须不能一致。然后重启master和slave。因为重启了master，所以binlog已经滚动了，不过这次不用再查看binlog坐标，因为重启造成的binlog日志移动不会影响slave。方式二：使用mysqldump进行备份恢复。这种方式简单的多，而且对于innodb表很适用，但是slave上恢复时速度慢，因为恢复时数据全是通过insert插入的。因为mysqldump可以进行定时点恢复甚至记住binlog的坐标，所以无需再手动获取binlog的坐标。shell> mysqldump -uroot -p --all-databases --master-data=2 >dump.db注意，--master-data选项将再dump.db中加入change master to相关的语句，值为2时，change master to语句是注释掉的，值为1或者没有提供值时，这些语句是直接激活的。同时，--master-data会锁定所有表(如果同时使用了--single-transaction，则不是锁所有表，详细内容请参见)。因此，可以直接从dump.db中获取到binlog的坐标。记住这个坐标。[root@xuexi ~]# grep -i -m 1 'change master to' dump.db -- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=154;然后将dump.db拷贝到slave上，使用mysql执行dump.db脚本即可。也可以直接在master上远程连接到slave上执行。例如：shell> mysql -uroot -p -h 192.168.100.150 -e 'source dump.db'方式三：使用xtrabackup进行备份恢复。这是三种方式中最佳的方式，安全性高、速度快。因为xtrabackup备份的时候会记录master的binlog的坐标，因此也无需手动获取binlog坐标。xtrabackup详细的备份方法见：注意：master和slave上都安装percona-xtrabackup。以全备份为例：innobackupex -u root -p /backup备份完成后，在/backup下生成一个以时间为名称的目录。其内文件如下：[root@xuexi ~]# ll /backup/2018-05-29_04-12-15 total 77872 -rw-r----- 1 root root 489 May 29 04:12 backup-my.cnf drwxr-x--- 2 root root 4096 May 29 04:12 backuptest -rw-r----- 1 root root 1560 May 29 04:12 ib_buffer_pool -rw-r----- 1 root root 79691776 May 29 04:12 ibdata1 drwxr-x--- 2 root root 4096 May 29 04:12 mysql drwxr-x--- 2 root root 4096 May 29 04:12 performance_schema drwxr-x--- 2 root root 12288 May 29 04:12 sys -rw-r----- 1 root root 22 May 29 04:12 xtrabackup_binlog_info -rw-r----- 1 root root 115 May 29 04:12 xtrabackup_checkpoints -rw-r----- 1 root root 461 May 29 04:12 xtrabackup_info -rw-r----- 1 root root 2560 May 29 04:12 xtrabackup_logfile其中xtrabackup_binlog_info中记录了binlog的坐标。记住这个坐标。[root@xuexi ~]# cat /backup/2018-05-29_04-12-15/xtrabackup_binlog_info master-bin.000002 154然后将备份的数据执行”准备”阶段。这个阶段不要求连接mysql，因此不用给连接选项。innobackupex --apply-log /backup/2018-05-29_04-12-15最后，将/backup目录拷贝到slave上进行恢复。恢复的阶段就是向MySQL的datadir拷贝。但注意，xtrabackup恢复阶段要求datadir必须为空目录。否则报错：[root@xuexi ~]# innobackupex ---back /backup/2018-05-29_04-12-15/ 180529 23:54:27 innobackupex: Starting the -back operation IMPORTANT: Please check that the -back run completes successfully. At the end of a successful -back run innobackupex prints "completed OK!". innobackupex version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5) Original data directory /data is not empty!所以，停止slave的mysql并清空datadir。service mysqld stop rm -rf /data/*恢复时使用的模式是”–-back”，选项后指定要恢复的源备份目录。恢复时因为不需要连接数据库，所以不用指定连接选项。1 2 [root@xuexi ~]# innobackupex ---back /backup/2018-05-29_04-12-15/ 180529 23:55:53 completed OK!恢复完成后，MySQL的datadir的文件的所有者和属组是innobackupex的调用者，所以需要改回mysql.mysql。shell> chown -R mysql.mysql /data启动slave，并查看恢复是否成功。shell> service mysqld start shell> mysql -uroot -p -e 'select * from backuptest.num_isam limit 10;' +----+ | n | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +----+4.3 slave开启复制经过前面的一番折腾，总算是把该准备的数据都准备到slave上，也获取到master上binlog的坐标(154)。现在还欠东风：连接master。连接master时，需要使用change master to提供连接到master的连接选项，包括user、port、password、binlog、position等。mysql> change master to master_host='192.168.100.20', master_port=3306, master_user='repl', master_password='P@ssword1!', master_log_file='master-bin.000002', master_log_pos=154;完整的change master to语法如下：CHANGE MASTER TO option [, option] ... option: | MASTER_HOST = 'host_name' | MASTER_USER = 'user_name' | MASTER_PASSWORD = 'password' | MASTER_PORT = port_num | MASTER_LOG_FILE = 'master_log_name' | MASTER_LOG_POS = master_log_pos | MASTER_AUTO_POSITION = {0|1} | RELAY_LOG_FILE = 'relay_log_name' | RELAY_LOG_POS = relay_log_pos | MASTER_SSL = {0|1} | MASTER_SSL_CA = 'ca_file_name' | MASTER_SSL_CAPATH = 'ca_directory_name' | MASTER_SSL_CERT = 'cert_file_name' | MASTER_SSL_CRL = 'crl_file_name' | MASTER_SSL_CRLPATH = 'crl_directory_name' | MASTER_SSL_KEY = 'key_file_name' | MASTER_SSL_CIPHER = 'cipher_list' | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}然后，启动IO线程和SQL线程。可以一次性启动两个，也可以分开启动。# 一次性启动、关闭 start slave; stop slave; # 单独启动 start slave io_thread; start slave sql_thread;至此，复制就已经可以开始工作了。当master写入数据，slave就会从master处进行复制。例如，在master上新建一个表，然后去slave上查看是否有该表。因为是DDL语句，它会写二进制日志，所以它也会复制到slave上。4.4 查看slave的信息change master to后，在slave的datadir下就会生成master.info文件和relay-log.info文件，这两个文件随着复制的进行，其内数据会随之更新。4.4.1 master.infomaster.info文件记录的是IO线程相关的信息，也就是连接master以及读取master binlog的信息。通过这个文件，下次连接master时就不需要再提供连接选项。以下是master.info的内容，每一行的意义见[root@xuexi ~]# cat /data/master.info 25 # 本文件的行数 master-bin.000002 # IO线程正从哪个master binlog读取日志 154 # IO线程读取到master binlog的位置 192.168.100.20 # master_host repl # master_user P@ssword1! # master_password 3306 # master_port 60 # master_retry，slave重连master的超时时间(单位秒) 0 0 30.000 0 86400 04.4.2 relay-log.inforelay-log.info文件中记录的是SQL线程相关的信息。以下是relay-log.info文件的内容，每一行的意义见[root@xuexi ~]# cat /data/relay-log.info 7 # 本文件的行数 ./slave-bin.000001 # 当前SQL线程正在读取的relay-log文件 4 # SQL线程已执行到的relay log位置 master-bin.000002 # SQL线程最近执行的操作对应的是哪个master binlog 154 # SQL线程最近执行的操作对应的是master binlog的哪个位置 0 # slave上必须落后于master多长时间 0 # 正在运行的SQL线程数 1 # 一种用于内部信息交流的ID，目前值总是14.4.3 show slave status在slave上执行show slave status可以查看slave的状态信息。信息非常多，每个字段的详细意义可参见mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: # slave上IO线程的状态，来源于show processlist Master_Host: 192.168.100.20 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: slave-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000002 Slave_IO_Running: No # IO线程的状态，此处为未运行且未连接状态 Slave_SQL_Running: No # SQL线程的状态，此处为未运行状态 Replicate_Do_DB: # 显式指定要复制的数据库 Replicate_Ignore_DB: # 显式指定要忽略的数据库 Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: # 以通配符方式指定要复制的表 Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 154 Until_Condition: None # start slave语句中指定的until条件， # 例如，读取到哪个binlog位置就停止 Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL # SQL线程执行过的位置比IO线程慢多少 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 # master的server id Master_UUID: Master_Info_File: /data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: # slave SQL线程的状态 Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.01 sec)因为太长，后面再列出show slave status时，将裁剪一些意义不大的行。再次回到上面show slave status的信息。除了那些描述IO线程、SQL线程状态的行，还有几个log_file和pos相关的行，如下所列。Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: slave-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000002 Exec_Master_Log_Pos: 154理解这几行的意义至关重要，前面因为排版限制，描述看上去有些重复。所以这里完整地描述它们：Master_Log_File：IO线程正在读取的master binlog；Read_Master_Log_Pos：IO线程已经读取到master binlog的哪个位置；Relay_Log_File：SQL线程正在读取和执行的relay log；Relay_Log_Pos：SQL线程已经读取和执行到relay log的哪个位置；Relay_Master_Log_File：SQL线程最近执行的操作对应的是哪个master binlog；Exec_Master_Log_Pos：SQL线程最近执行的操作对应的是master binlog的哪个位置。所以，(Relay_Master_Log_File, Exec_Master_log_Pos)构成一个坐标，这个坐标表示slave上已经将master上的哪些数据重放到自己的实例中，它可以用于下一次change master to时指定的binlog坐标。与这个坐标相对应的是slave上SQL线程的relay log坐标(Relay_Log_File, Relay_Log_Pos)。这两个坐标位置不同，但它们对应的数据是一致的。最后还有一个延迟参数Seconds_Behind_Master需要说明一下，它的本质意义是SQL线程比IO线程慢多少。如果master和slave之间的网络状况优良，那么slave的IO线程读速度和master写binlog的速度基本一致，所以这个参数也用来描述”SQL线程比master慢多少”，也就是说slave比master少多少数据，只不过衡量的单位是秒。但需要注意，这个参数的描述并不标准，只有在网速很好的时候做个大概估计，很多种情况下它的值都是0，即使SQL线程比IO线程慢了很多也是如此。4.4.4 slave信息汇总上面的master.info、relay-log.info和show slave status的状态都是刚连接上master还未启动IO thread、SQL thread时的状态。下面将显示已经进行一段正在执行复制的slave状态。首先查看启动io thread和sql thread后的状态。使用show processlist查看即可。mysql> start slave; mysql> show processlist; # slave上的信息，为了排版，简化了输出 +----+-------------+---------+--------------------------------------------------------+ | Id | User | Command | State | +----+-------------+---------+--------------------------------------------------------+ | 4 | root | Sleep | | | 7 | root | Query | starting | | 8 | system user | Connect | Waiting for master to send event | | 9 | system user | Connect | Slave has read all relay log; waiting for more updates | +----+-------------+---------+--------------------------------------------------------+可以看到：Id=8的线程负责连接master并读取binlog，它是IO 线程，它的状态指示”等待master发送更多的事件”；Id=9的线程负责读取relay log，它是SQL线程，它的状态指示”已经读取了所有的relay log”。再看看此时master上的信息。mysql> show processlist; # master上的信息，为了排版，经过了修改 +----+------+-----------------------+-------------+--------------------------------------+ | Id | User | Host | Command | State | +----+------+-----------------------+-------------+--------------------------------------+ | 4 | root | localhost | Query | starting | |----|------|-----------------------|-------------|--------------------------------------| | 16 | repl | 192.168.100.150:39556 | Binlog Dump | Master has sent all binlog to slave; | | | | | | waiting for more updates | +----+------+-----------------------+-------------+--------------------------------------+master上有一个Id=16的binlog dump线程，该线程的用户是repl。它的状态指示”已经将所有的binlog发送给slave了”。现在，在master上执行一个长事件，以便查看slave上的状态信息。仍然使用前面插入数值辅助表的存储过程，这次分别向两张表中插入一亿条数据(尽管去抽烟、喝茶，够等几分钟的。如果机器性能不好，请大幅减少插入的行数)。call proc_num1(100000000); call proc_num2(100000000);然后去slave上查看信息，如下。因为太长，已经裁剪了一部分没什么用的行。mysql> show slave status\G mysql: [Warning] Using a password on the command line interface can be insecure. *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.20 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 512685413 Relay_Log_File: slave-bin.000003 Relay_Log_Pos: 336989434 Relay_Master_Log_File: master-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 336989219 Slave_SQL_Running_State: Reading event from the relay log从中获取到的信息有：IO线程的状态SQL线程的状态IO线程读取到master binlog的哪个位置：512685413SQL线程已经执行到relay log的哪个位置：336989434SQL线程执行的位置对应于master binlog的哪个位置：336989219可以看出，IO线程比SQL线程超前了很多很多，所以SQL线程比IO线程的延迟较大。4.5 MySQL复制如何实现断开重连很多人以为change master to语句是用来连接master的，实际上这种说法是错的。连接master是IO线程的事情，change master to只是为IO线程连接master时提供连接参数。如果slave从来没连接过master，那么必须使用change master to语句来生成IO线程所需要的信息，这些信息记录在master.info中。这个文件是change master to成功之后立即生成的，以后启动IO线程时，IO线程都会自动读取这个文件来连接master，不需要先执行change master to语句。例如，可以随时stop slave来停止复制线程，然后再随时start slave，只要master.info存在，且没有人为修改过它，IO线程就一定不会出错。这是因为master.info会随着IO线程的执行而更新，无论读取到master binlog的哪个位置，都会记录下这个位置，如此一来，IO线程下次启动的时候就知道从哪里开始监控master binlog。前面还提到一个文件：relay-log.info文件。这个文件中记录的是SQL线程的相关信息，包括读取、执行到relay log的哪个位置，刚重放的数据对应master binlog的哪个位置。随着复制的进行，这个文件的信息会即时改变。所以，通过relay-log.info，下次SQL线程启动的时候就能知道从relay log的哪个地方继续读取、执行。如果不小心把relay log文件删除了，SQL线程可能会丢失了一部分相比IO线程延迟的数据。这时候，只需将relay-log.info中第4、5行记录的”SQL线程刚重放的数据对应master binlog的坐标”手动修改到master.info中即可，这样IO线程下次连接master就会从master binlog的这个地方开始监控。当然，也可以将这个坐标作为change master to的坐标来修改master.info。此外，当mysql实例启动时，默认会自动start slave，也就是MySQL一启动就自动开启复制线程。如果想要禁止这种行为，在配置文件中加上：[mysqld] skip-slave-start4.6 一些变量默认情况下，slave连接到master后会在slave的datadir下生成master.info和relay-log.info文件，但是这是可以通过设置变量来改变的。master-info-repository={TABLE|FILE}：master的信息是记录到文件master.info中还是记录到表mysql.slave_master_info中。默认为file。relay-log-info-repository={TABLE|FILE}：slave的信息是记录到文件relay-log.info中还是记录到表mysql.slave_relay_log_info中。默认为file。IO线程每次从master复制日志要写入到relay log中，但是它是先放在内存的，等到一定时机后才会将其刷到磁盘上的relay log文件中。刷到磁盘的时机可以由变量控制。另外，IO线程每次从master复制日志后都会更新master.info的信息，也是先更新内存中信息，在特定的时候才会刷到磁盘的master.info文件；同理SQL线程更新realy-log.info也是一样的。它们是可以通过变量来设置更新时机的。sync-relay-log=N：设置为大于0的数表示每从master复制N个事件就刷一次盘。设置为0表示依赖于操作系统的sync机制。sync-master-info=N：依赖于master-info-repository的设置，如果为file，则设置为大于0的值时表示每更新多少次master.info将其写入到磁盘的master.info中，设置为0则表示由操作系统来决定何时调用fdatasync()函数刷到磁盘。如果设置为table，则设置为大于0的值表示每更新多少次master.info就更新mysql.slave_master_info表一次，如果设置为0则表示永不更新该表。sync-relay-log-info=N：同上。5.一主多从一主多从有两种情况，结构图如下。配置一主多从时，需要考虑一件事：slave上是否要开启binlog? 如果不开启slave的binlog，性能肯定要稍微好一点。但是开启了binlog后，可以通过slave来备份数据，也可以在master宕机时直接将slave切换为新的master。此外，如果是上面第二种主从结构，这台slave必须开启binlog。可以将某台或某几台slave开启binlog，并在mysql动静分离的路由算法上稍微减少一点到这些slave上的访问权重。上面第一种一主多从的结构没什么可解释的，它和一主一从的配置方式完全一样，但是可以考虑另一种情况：向现有主从结构中添加新的slave。所以，稍后先介绍这种添加slave，再介绍第二种一主多从的结构。5.1 向现有主从结构中添加slave官方手册：例如在前文一主一从的实验环境下添加一台新的slave。因为新的slave在开始复制前，要有master上的基准数据，还要有master binlog的坐标。按照前文一主一从的配置方式，当然很容易获取这些信息，但这样会将master锁住一段时间(因为要备份基准数据)。深入思考一下，其实slave上也有数据，还有relay log以及一些仓库文件标记着数据复制到哪个地方。所以，完全可以从slave上获取基准数据和坐标，也建议这样做。仍然有三种方法从slave上获取基准数据：冷备份、mysqldump和xtrabackup。方法见前文。其实临时关闭一个slave对业务影响很小，所以我个人建议，新添加slave时采用冷备份slave的方式，不仅备份恢复的速度最快，配置成slave也最方便，这一点和前面配置”一主一从”不一样。但冷备份slave的时候需要注意几点：可以考虑将slave1完全shutdown再将整个datadir拷贝到新的slave2上。建议新的slave2配置文件中的”relay-log”的值和slave1的值完全一致，否则应该手动从slave2的relay-log.info中获取IO线程连接master时的坐标，并在slave2上使用change master to语句设置连接参数。 方法很简单，所以不做演示了。5.2 配置一主多从(从中有从)此处实现的一主多从是下面这种结构：这种结构对MySQL复制来说，是一个很好的提升性能的方式。对于只有一个master的主从复制结构，每多一个slave，意味着master多发一部分binlog，业务稍微繁忙一点时，这种压力会加剧。而这种一个主master、一个或多个辅助master的主从结构，非常有助于MySQL集群的伸缩性，对压力的适应性也很强。除上面一主多从、从中有从的方式可提升复制性能，还有几种提升MySQL复制性能的方式：将不同数据库复制到不同slave上。可以将master上的事务表(如InnoDB)复制为slave上的非事务表(如MyISAM)，这样slave上回放的速度加快，查询数据的速度在一定程度上也会提升。回到这种主从结构，它有些不同，master只负责传送日志给slave1、slave2和slave3，slave 2_1和slave 2_2的日志由slave2负责传送，所以slave2上也必须要开启binlog选项。此外，还必须开启一个选项--log-slave-updates让slave2能够在重放relay log时也写自己的binlog，否则slave2的binlog仅接受人为的写操作。问：slave能否进行写操作？重放relay log的操作是否会记录到slave的binlog中？在slave上没有开启read-only选项(只读变量)时，任何有写权限的用户都可以进行写操作，这些操作都会记录到binlog中。注意，read-only选项对具有super权限的用户以及SQL线程执行的重放写操作无效。默认这个选项是关闭的。mysql> show variables like "read_only"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+在slave上没有开启log-slave-updates和binlog选项时，重放relay log不会记录binlog。所以如果slave2要作为某些slave的master，那么在slave2上必须要开启log-slave-updates和binlog选项。为了安全和数据一致性，在slave2上还应该启用read-only选项。环境如下：以下是master、slave1和slave2上配置文件内容。# master上的配置 [mysqld] datadir=/data socket=/data/mysql.sock server_id=100 sync-binlog=1 log_bin=master-bin log-error=/data/err.log pid-file=/data/mysqld.pid # slave1上的配置 [mysqld] datadir=/data socket=/data/mysql.sock server_id=111 relay-log=slave-bin log-error=/data/err.log pid-file=/data/mysqld.pid log-slave-updates # 新增配置 log-bin=master-slave-bin # 新增配置 read-only=ON # 新增配置 # slave2上的配置 [mysqld] datadir=/data socket=/data/mysql.sock server_id=123 relay-log=slave-bin log-error=/data/err.log pid-file=/data/mysqld.pid read-only=ON因为slave2目前是全新的实例，所以先将slave1的基准数据备份到slave2。由于slave1自身就是slave，临时关闭一个slave对业务影响很小，所以直接采用冷备份slave的方式。# 在slave2上执行 shell> mysqladmin -uroot -p shutdown # 在slave1上执行： shell> mysqladmin -uroot -p shutdown shell> rsync -az --delete /data 192.168.100.19:/ shell> service mysqld start冷备份时，以下几点千万注意：因为slave2是slave1的从，所以在启动MySQL前必须将备份到slave2上的和复制有关的文件都删除。包括：(1).master.info。除非配置文件中指定了skip-slave-start，否则slave2将再次连接到master并作为master的slave。(2).relay-log.info。因为slave1启动后会继续执行relay log中的内容(如果有未执行的)，这时slave1会将这部分写入binlog并传送到slave2。(3).删除relay log文件。其实不是必须删除，但建议删除。(4).删除relay log index文件。(5).删除DATADIR/auto.conf。这个文件必须删除，因为这里面保留了mysql server的UUID，而master和slave的UUID必须不能一致。在启动mysql的时候，如果没有这个文件会自动生成自己的UUID并保存到auto.conf中。检查slave1上从master复制过来的专门用于复制的用户repl是否允许slave2连接。如果不允许，应该去master上修改这个用户。因为slave1是刚开启的binlog，所以slave2连接slave1时的binlog position应该指定为4。即使slave1不是刚开启的binlog，它在重启后也会滚动binlog。检查slave1上从master复制过来的专门用于复制的用户repl是否允许slave2连接。如果不允许，应该去master上修改这个用户。因为slave1是刚开启的binlog，所以slave2连接slave1时的binlog position应该指定为4。即使slave1不是刚开启的binlog，它在重启后也会滚动binlog。所以，在slave2上继续操作：shell> ls /data auto.cnf ib_buffer_pool ib_logfile1 performance_schema slave-bin.000005 backuptest ibdata1 master.info relay-log.info slave-bin.index err.log ib_logfile0 mysql slave-bin.000004 sys shell> rm -f /data/{master.info,relay-log.info,auto.conf,slave-bin*} shell> service mysqld start最后连上slave2，启动复制线程。shell> mysql -uroot -p mysql> change master to master_host='192.168.100.150', master_port=3306, master_user='repl', master_password='P@ssword1!', master_log_file='master-slave-bin.000001', master_log_pos=4; mysql> start slave; mysql> show slave status\G6.MySQL复制中一些常用操作6.1 筛选要复制的库和表默认情况下，slave会复制master上所有库。可以指定以下变量显式指定要复制的库、表和要忽略的库、表，也可以将其写入配置文件。Replicate_Do_DB: 要复制的数据库 Replicate_Ignore_DB: 不复制的数据库 Replicate_Do_Table: 要复制的表 Replicate_Ignore_Table: 不复制的表 Replicate_Wild_Do_Table: 通配符方式指定要复制的表 Replicate_Wild_Ignore_Table: 通配符方式指定不复制的表如果要指定列表，则多次使用这些变量进行设置。需要注意的是，尽管显式指定了要复制和忽略的库或者表，但是master还是会将所有的binlog传给slave并写入到slave的relay log中，真正负责筛选的slave上的SQL线程。另外，如果slave上开启了binlog，SQL线程读取relay log后会将所有的事件都写入到自己的binlog中，只不过对于那些被忽略的事件只记录相关的事务号等信息，不记录事务的具体内容。所以，如果之前设置了被忽略的库或表，后来取消忽略后，它们在取消忽略以前的变化是不会再重放的，特别是基于gtid的复制会严格比较binlog中的gtid。总之使用筛选的时候应该多多考虑是否真的要筛选，是否是永久筛选。6.2 reset slave和reset masterreset slave会删除master.info/relay-log.info和relay log，然后新生成一个relay log。但是change master to设置的连接参数还在内存中保留着，所以此时可以直接start slave，并根据内存中的change master to连接参数复制日志。reset slave all除了删除reset slave删除的东西，还删除内存中的change master to设置的连接信息。reset master会删除master上所有的二进制日志，并新建一个日志。在正常运行的主从复制环境中，执行reset master很可能导致异常状况。所以建议使用purge来删除某个时间点之前的日志(应该保证只删除那些已经复制完成的日志)。6.3 show slave hosts如果想查看master有几个slave的信息，可以使用show slave hosts。以下为某个master上的结果：mysql> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 111 | | 3306 | 11 | ff7bb057-2466-11e7-8591-000c29479b32 | | 1111 | | 3306 | 11 | 9b119463-24d2-11e7-884e-000c29867ec2 | +-----------+------+------+-----------+--------------------------------------+可以看到，该show中会显示server-id、slave的主机地址和端口号、它们的master_id以及这些slave独一无二的uuid号。其中show结果中的host显示结果是由slave上的变量report_host控制的，端口是由report_port控制的。例如，在slave2上修改其配置文件，添加report-host项后重启MySQL服务。[mysqld] report_host=192.168.100.19在slave1(前文的实验环境，slave1是slave2的master)上查看，host已经显示为新配置的项。mysql> show slave hosts; +-----------+----------------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+----------------+------+-----------+--------------------------------------+ | 111 | 192.168.100.19 | 3306 | 11 | ff7bb057-2466-11e7-8591-000c29479b32 | | 1111 | | 3306 | 11 | 9b119463-24d2-11e7-884e-000c29867ec2 | +-----------+----------------+------+-----------+--------------------------------------+6.4 多线程复制在老版本中，只有一个SQL线程读取relay log并重放。重放的速度肯定比IO线程写relay log的速度慢非常多，导致SQL线程非常繁忙，且实现到从库上延迟较大。没错，多线程复制可以解决主从延迟问题，且使用得当的话效果非常的好(关于主从复制延迟，是生产环境下最常见的问题之一，且没有很好的办法来避免。后文稍微介绍了一点方法)。在MySQL 5.6中引入了多线程复制(multi-thread slave，简称MTS)，这个多线程指的是多个SQL线程，IO线程还是只有一个。当IO线程将master binlog写入relay log中后，一个称为”多线程协调器(multithreaded slave coordinator)“会对多个SQL线程进行调度，让它们按照一定的规则去执行relay log中的事件。需要谨记于心的是，如果对多线程复制没有了解的很透彻，千万不要在生产环境中使用多线程复制。它的确带来了一些复制性能的提升，并且能解决主从超高延迟的问题，但随之而来的是很多的”疑难杂症”，这些”疑难杂症”并非是bug，只是需要多多了解之后才知道为何会出现这些问题以及如何解决这些问题。稍后会简单介绍一种多线程复制问题：gaps。通过全局变量slave-parallel-workers控制SQL线程个数，设置为非0正整数N，表示多加N个SQL线程，加上原有的共N+1个SQL线程。默认为0，表示不加任何SQL线程，即关闭多线程功能。mysql> show variables like "%parallel%"; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | slave_parallel_workers | 0 | +------------------------+-------+显然，多线程只有在slave上开启才有效，因为只有slave上才有SQL线程。另外，设置了该全局变量，需要重启SQL线程才生效，否则内存中还是只有一个SQL线程。例如，初始时slave上的processlist如下：设置slave_parallel_workers=2。mysql> set @@global.slave_parallel_workers=2; mysql> stop slave sql_thread; msyql> start slave sql_thread; mysql> show full processlist;可见多出了两个线程，其状态信息是”Waiting for an event from Coordinator”。虽然是多个SQL线程，但是复制时每个库只能使用一个线程(默认情况下，可以通过--slave-parallel-type修改并行策略)，因为如果一个库可以使用多个线程，多个线程并行重放relay log，可能导致数据错乱。所以应该设置线程数等于或小于要复制的库的数量，设置多了无效且浪费资源。6.4.1 多线程复制带来的不一致问题虽然多线程复制带来了一定的复制性能提升，但它也带来了很多问题，最严重的是一致性问题。完整的内容见。此处介绍其中一个最重要的问题。关于多线程复制，最常见也是开启多线程复制前最需要深入了解的问题是：由于多个SQL线程同时执行relay log中的事务，这使得slave上提交事务的顺序很可能和master binlog中记录的顺序不一致(除非指定变量slave_preserve_commit_order=1)。(注意：这里说的是事务而不是事件。因为MyISAM的binlog顺序无所谓，只要执行完了就正确，而且多线程协调器能够协调好这些任务。所以只需考虑innodb基于事务的binlog)举个简单的例子，master上事务A先于事务B提交，到了slave上因为多SQL线程的原因，可能事务B提交了事务A却还没提交。是否还记得show slave status中的Exec_master_log_pos代表的意义？它表示SQL线程最近执行的事件对应的是master binlog中的哪个位置。问题由此而来。通过show slave status，我们看到已经执行事件对应的坐标，它前面可能还有事务没有执行。而在relay log中，事务B记录的位置是在事务A之后的(和master一样)，于是事务A和事务B之间可能就存在一个孔洞(gap)，这个孔洞是事务A剩余要执行的操作。正常情况下，多线程协调器记录了一切和多线程复制相关的内容，它能识别这种孔洞(通过打低水位标记low-watermark)，也能正确填充孔洞。即使是在存在孔洞的情况下执行stop slave也不会有任何问题，因为在停止SQL线程之前，它会等待先把孔洞填充完。但危险因素太多，比如突然宕机、突然杀掉mysqld进程等等，这些都会导致孔洞持续下去，甚至可能因为操作不当而永久丢失这部分孔洞。那么如何避免这种问题，出现这种问题如何解决？1.如何避免gap。前面说了，多个SQL线程是通过协调器来调度的。默认情况下，可能会出现gap的情况，这是因为变量slave_preserve_commit_order的默认值为0。该变量指示协调器是否让每个SQL线程执行的事务按master binlog中的顺序提交。因此，将其设置为1，然后重启SQL线程即可保证SQL线程按序提交，也就不可能会有gap的出现。当事务B准备先于事务A提交的时候，它将一直等待。此时slave的状态将显示：1 2 Waiting for preceding transaction to commit # MySQL 5.7.8之后显示该状态 Waiting for its turn to commit # MySQL 5.7.8之前显示该状态尽管不会出现gap，但show slave status的Exec_master_log_pos仍可能显示在事务A的坐标之后。由于开启slave_preserve_commit_order涉及到不少操作，它还要求开启slave的binlog--log-bin(因此需要重启mysqld)，且开启重放relay log也记录binlog的行为--log-slave-updates，此外，还必须设置多线程的并行策略--slave-parallel-type=LOGICAL_CLOCK。shell> mysqladmin -uroot -p shutdown shell> cat /etc/my.cnf log_bin=slave-bin log-slave-updates slave_parallel_workers=1 slave_parallel_type=LOGICAL_CLOCK shell>service mysqld start2.如何处理已经存在的gap。方法之一，是从master上重新备份恢复到slave上，这种方法是处理不当的最后解决办法。正常的处理方法是，使用START SLAVE [SQL_THREAD] UNTIL SQL_AFTER_MTS_GAPS;，它表示SQL线程只有先填充gaps后才能启动。实际上，它涉及了两个操作：(1).填充gaps(2).自动停止SQL线程(所以之后需要手动启动SQL线程)一般来说，在填充完gaps之后，应该先reset slave移除已经执行完的relay log，然后再去启动sql_thread。6.4.2 多线程复制切换回单线程复制多线程的带来的问题不止gaps一种，所以没有深入了解多线程的情况下，千万不能在生产环境中启用它。如果想将多线程切换回单线程，可以执行如下操作：START SLAVE UNTIL SQL_AFTER_MTS_GAPS; SET @@GLOBAL.slave_parallel_workers = 0; START SLAVE SQL_THREAD;6.5 slave升级为master的大致操作当master突然宕机，有时候需要切换到slave，将slave提升为新的master。但对于master突然宕机可能造成的数据丢失，主从切换是无法解决的，它只是尽可能地不间断提供MySQL服务。假如现在有主服务器M，从服务器S1、S2，S1作为将来的新的master。在将S1提升为master之前，需要保证S1已经将relay log中的事件已经replay完成。即下面两个状态查看语句中SQL线程的状态显示为：”Slave has read all relay log; waiting for the slave I/O thread to update it”。show slave status; show processlist;停止S1上的IO线程和SQL线程，然后将S1的binlog清空(要求已启用binlog)。1 2 mysql> stop slave; mysql> reset master;在S2上停止IO线程和SQL线程，通过change master to修改master的指向为S1，然后再启动io线程和SQL线程。mysql> stop slave; mysql> change master to master_host=S1,... mysql> start slave;将应用程序原本指向M的请求修改为指向S1，如修改MySQL代理的目标地址。一般会通过MySQL Router、Amoeba、cobar等数据库中间件来实现。删除S1上的master.info、relay-log.info文件，否则下次S1重启服务器会继续以slave角色运行。将来M重新上线后，可以将其配置成S1的slave，然后修改应用程序请求的目标列表，添加上新上线的M，如将M加入到MySQL代理的读目标列表。注意：reset master很重要，如果不是基于GTID复制且开启了log-slave-updates选项时，S1在应用relay log的时候会将其写入到自己的binlog，以后S2会复制这些日志导致重复执行的问题。其实上面只是提供一种slave升级为Master的解决思路，在实际应用中环境可能比较复杂。例如，上面的S1是S2的master，这时S1如果没有设置为read-only，当M宕机时，可以不用停止S1，也不需要reset master等操作，受影响的操作仅仅只是S1一直无法连接M而已，但这对业务不会有多大的影响。相信理解了前面的内容，分析主从切换的思路应该也没有多大问题。6.6 指定不复制到slave上的语句前面说的可以用于指定不复制到slave上的库和表，但却没有筛选不复制到slave的语句。但有些特殊情况下，可能需要这种功能。例如，master上创建专门用于复制的用户repl，这种语句其实没有必要复制到slave上，甚至出于安全的考虑不应该复制到slave上。可以使用sql_log_bin变量对此进行设置，默认该变量的值为1，表示所有语句都写进binlog，从而被slave复制走。如果设置为0，则之后的语句不会写入binlog，从而实现”不复制某些语句到slave”上的功能。例如：屏蔽创建repl用户的语句。mysql> set sql_log_bin=0; mysql> create user repl@'%' identified by 'P@ssword1!'; mysql> grant replication slave on *.* to repl@'%'; mysql> set sql_log_bin=1;在使用该变量时，默认是会话范围内的变量，一定不能设置它的全局变量值，否则所有语句都将不写binlog。6.7 主从高延迟的解决思路slave通过IO线程获取master的binlog，并通过SQL线程来应用获取到的日志。因为各个方面的原因，经常会出现slave的延迟(即Seconds_Behind_Master的值)非常高(动辄几天的延迟是常见的，几个小时的延迟已经算短的)，使得主从状态不一致。一个很容易理解的延迟示例是：假如master串行执行一个大事务需要30分钟，那么slave应用这个事务也大约要30分钟，从master提交的那一刻开始，slave的延迟就是30分钟，更极端一点，由于binlog的记录时间点是在事务提交时，如果这个大事务的日志量很大，比如要传输10多分钟，那么很可能延迟要达到40分钟左右。而且更严重的是，这种延迟具有滚雪球的特性，从延迟开始，很容易导致后续加剧延迟。所以，第一个优化方式是不要在mysql中使用大事务，这是mysql主从优化的第一口诀。在回归正题，要解决slave的高延迟问题，先要知道Second_Behind_Master是如何计算延迟的：SQL线程比IO线程慢多少(其本质是NOW()减去Exec_Master_Log_Pos处设置的TIMESTAMP)。在主从网络状态良好的情况下，IO线程和master的binlog大多数时候都能保持一致(也即是IO线程没有多少延迟，除非事务非常大，导致二进制日志传输时间久，但mysql优化的一个最基本口诀就是大事务切成小事务)，所以在这种理想状态下，可以认为主从延迟说的是slave上的数据状态比master要延迟多少。它的计数单位是秒。1.从产生Binlog的master上考虑，可以在master上应用group commit的功能，并设置参数binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count，前者表示延迟多少秒才提交事务，后者表示要堆积多少个事务之后再提交。这样一来，事务的产生速度降低，slave的SQL线程相对就得到缓解。2.再者从slave上考虑，可以在slave上开启多线程复制(MTS)功能，让多个SQL线程同时从一个IO线程中取事务进行应用，这对于多核CPU来说是非常有效的手段。但是前面介绍多线程复制的时候说过，没有掌握多线程复制的方方面面之前，千万不要在生产环境中使用多线程复制，要是出现gap问题，很让人崩溃。3.最后从架构上考虑。主从延迟是因为slave跟不上master的速度，那么可以考虑对master进行节流控制，让master的性能下降，从而变相提高slave的能力。这种方法肯定是没人用的，但确实是一种方法，提供了一种思路，比如slave使用性能比master更好的硬件。另一种比较可取的方式是加多个中间slave层(也就是master->slaves->slaves)，让多个中间slave层专注于复制(也可作为非业务的他用，比如用于备份)。4.使用组复制或者Galera/PXC的多写节点，此外还可以设置相关参数，让它们对延迟自行调整。但一般都不需要调整，因为有默认设置。还有比较细致的方面可以降低延迟，比如设置为row格式的Binlog要比statement要好，因为不需要额外执行语句，直接修改数据即可。比如master设置保证数据一致性的日志刷盘规则(sync_binlog/innodb_flush_log_at_trx_commit设置为1)，而slave关闭binlog或者设置性能优先于数据一致性的binlog刷盘规则。再比如设置slave的隔离级别使得slave的锁粒度放大，不会轻易锁表(多线程复制时避免使用此方法)。还有很多方面，选择好的磁盘，设计好分库分表的结构等等，这些都是直接全局的，实在没什么必要在这里多做解释。MySQL 主从复制 半同步复制1.半同步复制半同步复制官方手册：默认情况下，MySQL的复制是异步的，master将新生成的binlog发送给各slave后，无需等待slave的ack回复(slave将接收到的binlog写进relay log后才会回复ack)，直接就认为这次DDL/DML成功了。半同步复制(semi-synchronous replication)是指master在将新生成的binlog发送给各slave时，只需等待一个(默认)slave返回的ack信息就返回成功。MySQL 5.7对半同步复制作了大改进，新增了一个master线程。在MySQL 5.7以前，master上的binlog dump线程负责两件事：dump日志给slave的io_thread；接收来自slave的ack消息。它们是串行方式工作的。在MySQL 5.7中，新增了一个专门负责接受ack消息的线程ack collector thread。这样master上有两个线程独立工作，可以同时发送binlog到slave和接收slave的ack。还新增了几个变量，其中最重要的是 ，它使得MySQL半同步复制有两种工作模型。解释如下。2.半同步复制的两种类型从MySQL 5.7.2开始，MySQL支持两种类型的半同步复制。这两种类型由变量 (MySQL 5.7.2之前没有该变量)控制，它有两种值：AFTER_SYNC和AFTER_COMMIT。在MySQL 5.7.2之后，默认值为AFTER_SYNC，在此版本之前，等价的类型为AFTER_COMMIT。这个变量控制的是master何时提交、何时接收ack以及何时回复成功信息给客户端的时间点。AFTER_SYNC模式：master将新的事务写进binlog(buffer)，然后发送给slave，再sync到自己的binlog file(disk)。之后才允许接收slave的ack回复，接收到ack之后才会提交事务，并返回成功信息给客户端。AFTER_COMMIT模式：master将新的事务写进binlog(buffer)，然后发送给slave，再sync到自己的binlog file(disk)，然后直接提交事务。之后才允许接收slave的ack回复，然后再返回成功信息给客户端。画图理解就很清晰。(前提：已经设置了sync_binlog=1，否则binlog刷盘时间由操作系统决定)再来分析下这两种模式的优缺点。AFTER_SYNCAFTER_COMMIT对于所有客户端来说，它们看到的数据是一样的，因为它们看到的数据都是在接收到slave的ack后提交后的数据。这种模式下，如果master突然故障，不会丢失数据，因为所有成功的事务都已经写进slave的relay log中了，slave的数据是最新的。不同客户端看到的数据可能是不一样的。对于发起事务请求的那个客户端，它只有在master提交事务且收到slave的ack后才能看到提交的数据。但对于那些非本次事务的请求客户端，它们在master提交后就能看到提交后的数据，这时候master可能还没收到slave的ack。如果master收到ack回复前，slave和master都故障了，那么将丢失这个事务中的数据。在MySQL 5.7.2之前，等价的模式是 ，在此版本之后，默认的模式为 ，该模式能最大程度地保证数据安全性，且性能上并不比 差。3.半同步复制插件介绍MySQL的半同步是通过加载google为MySQL提供的半同步插件 和 来实现的。其中前者是master上需要安装的插件，后者是slave上需要安装的插件。MySQL的插件位置默认存放在$basedir/lib/plugin目录下。例如，yum安装的mysql-server，插件目录为/usr/lib64/mysql/plugin。[root@xuexi ~]# find / -type f -name "semisync*" /usr/lib64/mysql/plugin/debug/semisync_master.so /usr/lib64/mysql/plugin/debug/semisync_slave.so /usr/lib64/mysql/plugin/semisync_master.so /usr/lib64/mysql/plugin/semisync_slave.so因为要加载插件，所以应该保证需要加载插件的MySQL的全局变量 已经设置为YES(默认值就是YES)，否则无法动态加载插件。mysql> select @@global.have_dynamic_loading; +-------------------------------+ | @@global.have_dynamic_loading | +-------------------------------+ | YES | +-------------------------------+3.1 MySQL中安装插件的方式安装插件有两种方式：1.在mysql环境中使用INSTALL PLUGIN语句临时安装；2.在配置文件中配置永久生效。INSTALL安装插件的语法为：Syntax: INSTALL PLUGIN plugin_name SONAME 'shared_library_name' UNINSTALL PLUGIN plugin_name例如，使用INSTALL语句在master上安装 插件。mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';配置文件中加载插件的方式为：[mysqld] plugin-load='plugin_name=shared_library_name'例如，配置文件中加载semisync_master.so插件。[mysqld] plugin-load="rpl_semi_sync_master=sermisync_master.so"如果需要加载多个插件，则插件之间使用分号分隔。例如，在本节的slave1既是slave，又是master，需要同时安装两个半同步插件。[mysqld] plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_sync_slave=semisync_slave.so"安装插件后，应该使用show plugins来查看插件是否真的激活。mysql> show plugins; +----------------------+--------+-------------+--------------------+---------+ | Name | Status | Type | Library | License | +----------------------+--------+-------------+--------------------+---------+ ...... | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | +----------------------+--------+-------------+--------------------+---------+或者查看information_schema.plugins表获取更详细的信息。mysql> select * from information_schema.plugins where plugin_name like "%semi%"\G *************************** 1. row *************************** PLUGIN_NAME: rpl_semi_sync_master PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: REPLICATION PLUGIN_TYPE_VERSION: 4.0 PLUGIN_LIBRARY: semisync_master.so PLUGIN_LIBRARY_VERSION: 1.7 PLUGIN_AUTHOR: He Zhenxing PLUGIN_DESCRIPTION: Semi-synchronous replication master PLUGIN_LICENSE: GPL LOAD_OPTION: ON 1 row in set (0.00 sec)插件装载完成后，半同步功能还未开启，需要手动设置它们启动，或者写入配置文件永久生效。# 开启master的半同步 mysql> set @@global.rpl_semi_sync_master_enabled=1; # 开启slave半同步 mysql> set @@globale.rpl_semi_sync_slave_enabled=1;或者配合插件加载选项一起写进配置文件永久开启半同步功能。[mysqld] rpl_semi_sync_master_enabled=1 [mysqld] rpl_semi_sync_slave_enabled=13.2 半同步插件相关的变量安装了 和 后，这两个插件分别提供了几个变量。mysql> show global variables like "%semi%"; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_for_slave_count | 1 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_SYNC | | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +-------------------------------------------+------------+下面还多给了两个和半同步相关的状态变量的解释，可以通过show status like %semi%;查看它们。master相关的变量：slave相关的变量：master相关的变量：①.Rpl_semi_sync_master_clients：(状态变量)master所拥有的半同步复制slave的主机数量。②.Rpl_semi_sync_master_status：(状态变量)master当前是否以半同步复制状态工作(ON)，OFF表示降级为了异步复制。③.rpl_semi_sync_master_enabled：master上是否启用了半同步复制。④.rpl_semi_sync_master_timeout：等待slave的ack回复的超时时间，默认为10秒。⑤.rpl_semi_sync_master_trace_level：半同步复制时master的调试级别。⑥.rpl_semi_sync_master_wait_for_slave_count：master在超时时间内需要收到多少个ack回复才认为此次DML成功，否则就降级为异步复制。该变量在MySQL5.7.3才提供，在此之前的版本都默认为收到1个ack则确认成功，且不可更改。MySQL 5.7.3之后该变量的默认值也是1。⑦.rpl_semi_sync_master_wait_no_slave：值为ON(默认)或者OFF。ON表示master在超时时间内如果未收到指定数量的ack消息，则会一直等待下去直到收满ack，即一直采用半同步复制方式，不会降级；OFF表示如果在超时时间内未收到指定数量的ack，则超时时间一过立即降级为异步复制。①.Rpl_semi_sync_master_clients：(状态变量)master所拥有的半同步复制slave的主机数量。②.Rpl_semi_sync_master_status ：(状态变量)master当前是否以半同步复制状态工作(ON)，OFF表示降级为了异步复制。③.rpl_semi_sync_master_enabled：master上是否启用了半同步复制。④.rpl_semi_sync_master_timeout：等待slave的ack回复的超时时间，默认为10秒。⑤.rpl_semi_sync_master_trace_level：半同步复制时master的调试级别。⑥.rpl_semi_sync_master_wait_for_slave_count：master在超时时间内需要收到多少个ack回复才认为此次DML成功，否则就降级为异步复制。该变量在MySQL5.7.3才提供，在此之前的版本都默认为收到1个ack则确认成功，且不可更改。MySQL 5.7.3之后该变量的默认值也是1。⑦.rpl_semi_sync_master_wait_no_slave：值为ON(默认)或者OFF。ON表示master在超时时间内如果未收到指定数量的ack消息，则会一直等待下去直到收满ack，即一直采用半同步复制方式，不会降级；OFF表示如果在超时时间内未收到指定数量的ack，则超时时间一过立即降级为异步复制。更官方的解释是：当设置为ON时，即使状态变量Rpl_semi_sync_master_clients中的值小于rpl_semi_sync_master_wait_for_slave_count，Rpl_semi_sync_master_status依旧为ON；当设置为OFF时，如果clients的值小于count的值，则Rpl_semi_sync_master_status立即变为OFF。通俗地讲，就是在超时时间内，如果slave宕机的数量超过了应该要收到的ack数量，master是否降级为异步复制。该变量在MySQL 5.7.3之前似乎没有效果，因为默认设置为ON时，超时时间内收不到任何ack时仍然会降级为异步复制。⑧.rpl_semi_sync_master_wait_point：控制master上commit、接收ack、返回消息给客户端的时间点。值为和，该选项是MySQL5.7.2后引入的，默认值为，在此版本之前，等价于使用了模式。关于这两种模式，见前文对两种半同步类型的分析。slave相关的变量：①.rpl_semi_sync_slave_enabled：slave是否开启半同步复制。②.rpl_semi_sync_slave_trace_level：slave的调试级别。4.配置半同步复制需要注意的是，”半同步”是同步/异步类型的一种情况，既可以实现半同步的传统复制，也可以实现半同步的GTID复制。其实半同步复制是基于异步复制的，它是在异步复制的基础上通过加载半同步插件的形式来实现半同步性的。此处以全新的环境进行配置，方便各位道友”依葫芦画瓢”。本文实现如下拓扑图所示的半同步传统复制。如果要实现半同步GTID复制，也只是在gtid复制的基础上改改配置文件而已。具体环境：因为都是全新的实例环境，所以无需考虑基准数据和binlog坐标的问题。如果开始测试前，已经在master上做了一些操作，或者创建了一些新数据，那么请将master上的数据恢复到各slave上，并获取master binlog的坐标，具体操作方法可参见前文：将slave恢复到master指定的坐标。4.1 半同步复制的配置文件首先提供各MySQL Server的配置文件。以下是master的配置文件。[mysqld] datadir=/data socket=/data/mysql.sock log-error=/data/error.log pid-file=/data/mysqld.pid log-bin=/data/master-bin sync-binlog=1 server-id=100 plugin-load="rpl_semi_sync_master=semisync_master.so" rpl_semi_sync_master_enabled=1以下是slave1的配置文件，注意slave1同时还充当着slave2和slave3的master的角色。[mysqld] datadir=/data socket=/data/mysql.sock log-error=/data/error.log pid-file=/data/mysqld.pid log-bin=/data/master-bin sync-binlog=1 server-id=110 relay-log=/data/slave-bin log-slave-updates plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" rpl_semi_sync_slave_enabled=1 rpl_semi_sync_master_enabled=1以下是slave2和slave3的配置文件，它们配置文件除了server-id外都一致。[mysqld] datadir=/data socket=/data/mysql.sock log-error=/data/error.log pid-file=/data/mysqld.pid server-id=120 # slave3的server-id=130 relay-log=/data/slave-bin plugin-load="rpl_semi_sync_slave=semisync_slave.so" rpl_semi_sync_slave_enabled=1 read-only=on4.2 启动复制线程现在master上创建一个专门用于复制的用户。mysql> create user repl@'192.168.100.%' identified by 'P@ssword1!'; mysql> grant replication slave on *.* to repl@'192.168.100.%';因为master和所有的slave都是全新的实例，所以slave上指定的binlog坐标可以从任意位置开始。不过刚才master上创建了一个用户，也会写binlog，所以建议还是从master的第一个binlog的position=4开始。以下是slave1上的change master to参数：mysql> change master to master_host='192.168.100.21', master_port=3306, master_user='repl', master_password='P@ssword1!', master_log_file='master-bin.000001', master_log_pos=4;以下是slave2和slave3的change master to参数：mysql> change master to master_host='192.168.100.22', master_port=3306, master_user='repl', master_password='P@ssword1!', master_log_file='master-bin.000001', master_log_pos=4;启动各slave上的两个SQL线程。mysql> start slave;一切就绪后，剩下的事情就是测试。在master上对数据做一番修改，然后查看是否会同步到slave1、slave2、slave3上。5.半同步复制的状态信息首先是semisync相关的可修改变量，这几个变量在已经解释过了。例如以下是开启了半同步复制后的master上的semisync相关变量。mysql> show global variables like "%semi%"; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_for_slave_count | 1 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_SYNC | +-------------------------------------------+------------+关于半同步复制，还有几个状态变量很重要。例如，以下是master上关于semi_sync的状态变量信息。mysql> show status like "%semi%"; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | # 注意行1 | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 5 | | Rpl_semi_sync_master_no_times | 1 | | Rpl_semi_sync_master_no_tx | 1 | | Rpl_semi_sync_master_status | ON | # 注意行2 | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 384 | | Rpl_semi_sync_master_tx_wait_time | 1537 | | Rpl_semi_sync_master_tx_waits | 4 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 4 | +--------------------------------------------+-------+除了上面标注”注意行”的变量，其他都无需关注，而且其中有一些是废弃了的状态变量。Rpl_semi_sync_master_clients是该master所连接到的slave数量。Rpl_semi_sync_master_status是该master的半同步复制功能是否开启。在有些时候半同步复制会降级为异步复制，这时它的值为OFF。以下是slave1上关于semi_sync的状态变量信息。mysql> show status like "%semi%"; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 2 | # 注意行1 | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 8 | | Rpl_semi_sync_master_no_times | 2 | | Rpl_semi_sync_master_no_tx | 4 | | Rpl_semi_sync_master_status | ON | # 注意行2 | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 399 | | Rpl_semi_sync_master_tx_wait_time | 1199 | | Rpl_semi_sync_master_tx_waits | 3 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 3 | | Rpl_semi_sync_slave_status | ON | # 注意行3 +--------------------------------------------+-------+此外，从MySQL的错误日志、show slave status也能获取到一些半同步复制的状态信息。下一节测试半同步复制再说明。6.测试半同步复制(等待、降级问题)前面已经搭建好了下面的半同步复制结构。|------> slave2 master ---> slave1 --- |------> slave3下面来测试半同步复制降级为异步复制的问题，借此来观察一些semisync的状态变化。首先，只停掉slave2或slave3中其中一个io线程的话，slave1是不会出现降级的，因为默认的半同步复制只需等待一个ack回复即可返回成功信息。如果同时停掉slave2和slave3的io线程，当master更新数据后，slave1在10秒(默认)之后将降级为异步复制。如下：在slave2和slave3上执行：mysql> stop slave io_thread;在master上执行：create database test1; create table test1.t(id int); insert into test1.t values(33);在slave1上查看(在上面的步骤之后的10秒内查看)：mysql> show status like "%semi%"; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | # clients=0 | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 8 | | Rpl_semi_sync_master_no_times | 2 | | Rpl_semi_sync_master_no_tx | 4 | | Rpl_semi_sync_master_status | ON | # status=ON | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 399 | | Rpl_semi_sync_master_tx_wait_time | 1199 | | Rpl_semi_sync_master_tx_waits | 3 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 1 | | Rpl_semi_sync_master_yes_tx | 3 | | Rpl_semi_sync_slave_status | ON | +--------------------------------------------+-------+可以看到在这一小段时间内，slave1还是半同步复制。此时用show slave status查看slave1。# slave1上执行 mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.21 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000004 Read_Master_Log_Pos: 1762 Relay_Log_File: slave-bin.000005 Relay_Log_Pos: 1729 Relay_Master_Log_File: master-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes ................................................ Slave_SQL_Running_State: Waiting for semi-sync ACK from slave ................................................此时slave的SQL线程状态是Waiting for semi-sync ACK from slave。但10秒之后再查看。mysql> show status like "%semi%"; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 8 | | Rpl_semi_sync_master_no_times | 3 | | Rpl_semi_sync_master_no_tx | 5 | | Rpl_semi_sync_master_status | OFF | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 399 | | Rpl_semi_sync_master_tx_wait_time | 1199 | | Rpl_semi_sync_master_tx_waits | 3 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 3 | | Rpl_semi_sync_slave_status | ON | +--------------------------------------------+-------+发现slave1已经关闭半同步功能了，也就是说降级为异步复制了。此时查看slave1的错误日志。2018-06-11T03:43:21.765384Z 4 [Warning] Timeout waiting for reply of binlog (file: master-bin.000001, pos: 2535), semi-sync up to file master-bin.000001, position 2292. 2018-06-11T03:43:21.765453Z 4 [Note] Semi-sync replication switched OFF.它先记录了当前slave2/slave3中已经同步到slave1的哪个位置。然后将Semi-sync复制切换为OFF状态，即降级为异步复制。在下次slave2或slave3启动IO线程时，slave1将自动切换回半同步复制，并发送那些未被复制的binlog。MySQL 主从复制 基于GTID复制相比传统的MySQL复制，gtid复制无论是配置还是维护都要轻松的多。本文对gtid复制稍作介绍。MySQL基于GTID复制官方手册：1.gtid基本概念传统的基于binlog position复制的方式有个严重的缺点：如果slave连接master时指定的binlog文件错误或者position错误，会造成遗漏或者重复，很多时候前后数据是有依赖性的，这样就会出错而导致数据不一致。从MYSQL5.6开始，mysql开始支持GTID复制。GTID的全称是global transaction id，表示的是全局事务ID。GTID的分配方式为uuid:trans_id，其中：uuid是每个mysql服务器都唯一的，记录在$datadir/auto.cnf中。如果复制结构中，任意两台服务器uuid重复的话(比如直接冷备份时，auto.conf中的内容是一致的)，在启动复制功能的时候会报错。这时可以删除auto.conf文件再重启mysqld。mysql> show variables like "%uuid%"; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | a126fcb6-3706-11e8-b1d5-000c294ebf0d | +---------------+--------------------------------------+ 1 row in set (0.00 sec) mysql> \! cat /data/auto.cnf [auto] server-uuid=a126fcb6-3706-11e8-b1d5-000c294ebf0dtrans_id是事务ID，可以唯一标记某MySQL服务器上执行的某个事务。事务号从1开始，每提交一个事务，事务号加1。例如”gtid_executed 5ad9cb8e-2092-11e7-ac95-000c29bf823d:1-6”，表示该server_uuid上执行了从1到6的事务。2.gtid的生命周期gtid的生命周期对于配置和维护基于gtid的复制至关重要。所以，请尽可能理解以下几个过程。gtid在master和slave上是一直持久化保存(即使删除了日志，也会记录到Previous_GTID中)的。它在master和slave上的生命周期如下：客户端发送DDL/DML给master上，master首先对此事务生成一个唯一的gtid，假如为uuid_xxx:1，然后立即执行该事务中的操作。注意，主从复制的情况下，sync-binlog基本上都会设置为1，这表示在每次提交事务时将缓存中的binlog刷盘。所以，在事务提交前，gtid以及事务相关操作的信息都在缓存中，提交后它们才写入到binlog file中，然后才会被dump线程dump出去。换句话说，只有提交了的事务，gtid和对应的事务操作才会记录到binlog文件中。记录的格式是先记录gtid，紧跟着再记录事务相关的操作。当binlog传送到relay log中后，slave上的SQL线程首先读取该gtid，并设置变量的值为该gtid，表示下一个要操作的事务是该gtid。是基于会话的，不同会话的gtid_next不同。随后slave检测该gtid在自己的binlog中是否存在。如果存在，则放弃此gtid事务；如果不存在，则将此gtid写入到自己的binlog中，然后立刻执行该事务，并在自己的binlog中记录该事务相关的操作。当binlog传送到relay log中后，slave上的SQL线程首先读取该gtid，并设置变量 的值为该gtid，表示下一个要操作的事务是该gtid。 是基于会话的，不同会话的gtid_next不同。随后slave检测该gtid在自己的binlog中是否存在。如果存在，则放弃此gtid事务；如果不存在，则将此gtid写入到自己的binlog中，然后立刻执行该事务，并在自己的binlog中记录该事务相关的操作。注意，slave上replay的时候，gtid不是提交后才写到自己的binlog file的，而是判断gtid不存在后立即写入binlog file。通过这种在执行事务前先检查并写gtid到binlog的机制，不仅可以保证当前会话在此之前没有执行过该事务，还能保证没有其他会话读取了该gtid却没有提交。因为如果其他会话读取了该gtid会立即写入到binlog(不管是否已经开始执行事务)，所以当前会话总能读取到binlog中的该gtid，于是当前会话就会放弃该事务。总之，一个gtid事务是决不允许多次执行、多个会话并行执行的。slave在重放relay log中的事务时，不会自己生成gtid，所以所有的slave(无论是何种方式的一主一从或一主多从复制架构)通过重放relay log中事务获取的gtid都来源于master，并永久保存在slave上。3.基于gtid复制的好处从上面可以看出，gtid复制的优点大致有：保证同一个事务在某slave上绝对只执行一次，没有执行过的gtid事务总是会被执行。不用像传统复制那样保证binlog的坐标准确，因为根本不需要binlog以及坐标。故障转移到新的master的时候很方便，简化了很多任务。很容易判断master和slave的数据是否一致。只要master上提交的事务在slave上也提交了，那么一定是一致的。当然，MySQL提供了选项可以控制跳过某些gtid事务，防止slave第一次启动复制时执行master上的所有事务而导致耗时过久。虽然对于row-based和statement-based的格式都能进行gtid复制，但建议采用row-based格式。4.配置一主一从的gtid复制环境：因为是用作master和slave的mysql实例都是全新环境，所以这里简单配置一下即可。master的配置文件：[mysqld] datadir=/data socket=/data/mysql.sock log-bin=/data/master-bin # 必须项 sync-binlog=1 # 建议项 binlog_format=row # 建议项 server-id=100 # 必须项 log-error=/data/error.log pid-file=/data/mysqld.pid enforce_gtid_consistency=on # gtid复制需要加上的必须项 gtid_mode=on # gtid复制需要加上的必须项关于后面的两项，是gtid复制所必须开启的项，这里指定它开启就行了，它们的意义以及更多gtid相关的选项见后文解释。slave的配置文件：[mysqld] datadir=/data socket=/data/mysql.sock log-bin=/data/master-slave-bin # mysql 5.6必须项，mysql 5.7非必须项 sync-binlog=1 # 建议项 binlog_format=row # 建议项 relay-log=/data/slave-bin # 必须项 server-id=110 # 必须项 log-error=/data/error.log pid-file=/data/mysqld.pid enforce_gtid_consistency=on # 必须项 gtid_mode=on # 必须项我的环境是mysql 5.7，如果是mysql 5.6，那么在上面两个配置文件中需要加上log-slave-updates选项。重启master和slave后，在master上创建一个用于复制的用户repl。# master上执行 mysql> grant replication slave on *.* to repl@'192.168.100.%' identified by 'P@ssword1!';因为master上的binlog没有删除过，所以在slave上直接change master to配置连接参数。# slave上执行 mysql> change master to master_host='192.168.100.21', master_port=3306, master_auto_position=1; # gtid复制必须设置此项因为是MySQL 5.7，没有在change master to语句中加入user和password项，而是在start slave语句中使用，否则会警告。现在启动slave上的两个复制线程。# slave上执行 mysql> start slave user='repl' password='P@ssword1!';查看io线程和sql线程是否正常。# slave上执行，为了排版，缩减了一些无关紧要的字段 mysql> show processlist; +----+-------------+---------+--------------------------------------------------------+ | Id | User | Command | State | +----+-------------+---------+--------------------------------------------------------+ | 9 | root | Query | starting | | 10 | system user | Connect | Waiting for master to send event | | 11 | system user | Connect | Slave has read all relay log; waiting for more updates | +----+-------------+---------+--------------------------------------------------------+最后验证gtid复制是否生效。在master上插入一些数据。这里使用中使用的存储过程proc_num1和proc_num2分别向数值辅助表backup.num_isam和backup.num_innodb中插入一些数据，该存储过程的代码见：# 向MyISAM数值辅助表backup.num_isam插入100W行数据 call proc_num1(1000000); # 向InnoDB数值辅助表backup.num_innodb插入100W行数据 call proc_num2(1000000);在slave上查看slave的状态，以下是同步结束后的状态信息。# slave上执行： mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.21 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000004 Read_Master_Log_Pos: 10057867 Relay_Log_File: slave-bin.000003 Relay_Log_Pos: 457 Relay_Master_Log_File: master-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 10057867 Relay_Log_Space: 10058586 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 100 Master_UUID: a659234f-6aea-11e8-a361-000c29ed4cf4 Master_Info_File: /data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: a659234f-6aea-11e8-a361-000c29ed4cf4:1-54 Executed_Gtid_Set: a659234f-6aea-11e8-a361-000c29ed4cf4:1-54 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:5.添加新的slave到gtid复制结构中GTID复制是基于事务ID的，确切地说是binlog中的GTID，所以事务ID对GTID复制来说是命脉。当master没有删除过任何binlog时，可以随意地向复制结构中添加新的slave，因为slave会复制所有的binlog到自己relay log中并replay。这样的操作尽管可能速度不佳，但胜在操作极其简便。当master删除过一部分binlog后，在向复制结构中添加新的slave时，必须先获取到master binlog中当前已记录的第一个gtid之前的所有数据，然后恢复到slave上。只有slave上具有了这部分基准数据，才能保证和master的数据一致性。而在实际环境中，往往会定期删除一部分binlog。所以，为了配置更通用的gtid复制环境，这里把前文的master的binlog给purge掉一部分。目前master上的binlog使用情况如下，不难发现绝大多数操作都集中在master-bin.000004这个binlog中。[root@xuexi ~]# ls -l /data/*bin* -rw-r----- 1 mysql mysql 177 Jun 8 15:07 /data/master-bin.000001 -rw-r----- 1 mysql mysql 727 Jun 8 15:42 /data/master-bin.000002 -rw-r----- 1 mysql mysql 177 Jun 9 09:50 /data/master-bin.000003 -rw-r----- 1 mysql mysql 10057867 Jun 9 10:17 /data/master-bin.000004 -rw-r----- 1 mysql mysql 96 Jun 9 09:50 /data/master-bin.indexpurge已有的binlog。mysql> flush logs; mysql> purge master logs to 'master-bin.000005'; [root@xuexi ~]# cat /data/master-bin.index /data/master-bin.000005但无论master是否purge过binlog，配置基于gtid的复制都极其方便，而且方法众多(只要理解了GTID的生命周期，可以随意折腾，基本上都能很轻松地维护好)，这是它”迷人”的优点。现在的测试环境是这样的：其中slave2的配置文件和slave1的配置文件完全相同：[mysqld] datadir=/data socket=/data/mysql.sock log-bin=/data/master-slave-bin # 必须项 sync-binlog=1 # 建议项 binlog_format=row # 建议项 relay-log=/data/slave-bin # 必须项 server-id=110 # 必须项 log-error=/data/error.log pid-file=/data/mysqld.pid enforce_gtid_consistency=on # 必须项 gtid_mode=on # 必须项1.备份master。我选择的是xtrabackup的innobackupex工具，因为它速度快，操作简单，而且在线备份也比较安全。如果不知道xtrabackup备份的使用方法，见我的另一篇文章：。当然，你也可以采用mysqldump和冷备份的方式，因为gtid复制的特性，这些备份方式也都很安全。# master上执行，备份所有数据： [root@xuexi ~]# mkdir /backdir # 备份目录 [root@xuexi ~]# innobackupex -uroot -pP@ssword1! -S /data/mysql.sock /backdir/ # 准备数据 [root@xuexi ~]# innobackupex --apply-log /backdir/2018-06-09_20-02-24/ # 应用数据 [root@xuexi ~]# scp -pr /backdir/2018-06-09_20-02-24/ 192.168.100.23:/tmp2.将备份恢复到slave2。在slave2上执行：[root@xuexi ~]# systemctl stop mysqld [root@xuexi ~]# rm -rf /data/* # 恢复前必须先清空数据目录 [root@xuexi ~]# innobackupex ---back /tmp/2018-06-09_20-02-24/ # 恢复备份数据 [root@xuexi ~]# chown -R mysql.mysql /data [root@xuexi ~]# systemctl start mysqld3.设置gtid_purged，连接master，开启复制功能。由于xtrabackup备份数据集却不备份binlog，所以必须先获取此次备份结束时的最后一个事务ID，并在slave上明确指定跳过这些事务，否则slave会再次从master上复制这些binlog并执行，导致数据重复执行。可以从slave2数据目录中的xtrabackup_info文件中获取。如果不是xtrabackup备份的，那么可以直接从master的show global variables like "gtid_executed";中获取，它表示master中已执行过的事务。[root@xuexi ~]# cat /data/xtrabackup_info uuid = fc3de8c1-6bdc-11e8-832d-000c29ed4cf4 name = tool_name = innobackupex tool_command = -uroot -pP@ssword1! -S /data/mysql.sock /backdir/ tool_version = 2.4.11 ibbackup_version = 2.4.11 server_version = 5.7.22-log start_time = 2018-06-09 20:02:28 end_time = 2018-06-09 20:02:30 lock_time = 0 binlog_pos = filename 'master-bin.000005', position '194', GTID of the last change 'a659234f-6aea-11e8-a361-000c29ed4cf4:1-54' innodb_from_lsn = 0 innodb_to_lsn = 51235233 partial = N incremental = N format = file compact = N compressed = N encrypted = N其中binlog_pos中的GTID对应的就是已备份的数据对应的事务。换句话说，这里的gtid集合1-54表示这54个事务不需要进行复制。或者在master上直接查看executed的值，注意不是gtid_purged的值，master上的gtid_purged表示的是曾经删除掉的binlog。mysql> show global variables like '%gtid%'; +----------------------------------+-------------------------------------------+ | Variable_name | Value | +----------------------------------+-------------------------------------------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | a659234f-6aea-11e8-a361-000c29ed4cf4:1-54 | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | a659234f-6aea-11e8-a361-000c29ed4cf4:1-54 | | session_track_gtids | OFF | +----------------------------------+-------------------------------------------+可以在启动slave线程之前使用gtid_purged变量来指定需要跳过的gtid集合。但因为要设置gtid_purged必须保证全局变量gtid_executed为空，所以先在slave上执行reset master(注意，不是reset slave)，再设置gtid_purged。# slave2上执行： mysql> reset master; mysql> set @@global.gtid_purged='a659234f-6aea-11e8-a361-000c29ed4cf4:1-54';设置好gtid_purged之后，就可以开启复制线程了。mysql> change master to master_host='192.168.100.21', master_port=3306, master_auto_position=1; mysql> start slave user='repl' password='P@ssword1!';查看slave的状态，看是否正确启动了复制功能。如果没错，再在master上修改一部分数据，检查是否同步到slave1和slave2。4.回到master，purge掉已同步的binlog。当slave指定gtid_purged并实现了同步之后，为了下次重启mysqld实例不用再次设置gtid_purged(甚至可能会在启动的时候自动开启复制线程)，所以应该去master上将已经同步的binlog给purged掉。# master上执行： mysql> flush logs; # flush之后滚动到新的日志master-bin.000006 # 在确保所有slave都复制完000006之前的所有事务后，purge掉旧日志 mysql> purge master logs to "master-bin.000006";6.GTID复制相关的状态信息和变量6.1 show slave status中和gtid复制相关的状态行Retrieved_Gtid_Set: a659234f-6aea-11e8-a361-000c29ed4cf4:1-54 Executed_Gtid_Set: a659234f-6aea-11e8-a361-000c29ed4cf4:1-54 Auto_Position: 1其中：Retrieved_Gtid_Set：在开启了gtid复制(即gtid_mode=on)时，slave在启动io线程的时候会检查自己的relay log，并从中检索出gtid集合。也就是说，这代表的是slave已经从master中复制了哪些事务过来。检索出来的gtid不会再请求master发送过来。Executed_Gtid_Set：在开启了gtid复制(即gtid_mode=on)时，它表示已经向自己的binlog中写入了哪些gtid集合。注意，这个值是根据一些状态信息计算出来的，并非binlog中能看到的那些。举个特殊一点的例子，可能slave的binlog还是空的，但这里已经显示一些已执行gtid集合了。Auto_Position：开启gtid时是否自动获取binlog坐标。1表示开启，这是gtid复制的默认值。6.2 binlog中关于gtid的信息例如：[root@xuexi ~]# mysqlbinlog /data/master-bin.000007 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #180610 1:34:08 server id 100 end_log_pos 123 CRC32 0x4a6e9510 Start: binlog v 4, server v 5.7.22-log created 180610 1:34:08 # Warning: this binlog is either in use or was not closed properly. BINLOG ' kA8cWw9kAAAAdwAAAHsAAAABAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA ARCVbko= '/*!*/; # at 123 #180610 1:34:08 server id 100 end_log_pos 194 CRC32 0x0f6ba409 Previous-GTIDs # a659234f-6aea-11e8-a361-000c29ed4cf4:1-57 #### 注意行1 # at 194 #180610 2:06:31 server id 100 end_log_pos 259 CRC32 0xfef9194e GTID last_committed=0 sequence_number=1 rbr_only=no #### 注意行2 SET @@SESSION.GTID_NEXT= 'a659234f-6aea-11e8-a361-000c29ed4cf4:58'/*!*/; #### 注意行3 # at 259 #180610 2:06:31 server id 100 end_log_pos 359 CRC32 0x5a561d94 Query thread_id=2 exec_time=0 error_code=0 use `backup`/*!*/; SET TIMESTAMP=1528567591/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create table t1(n int) /*!*/; # at 359 #180610 2:09:36 server id 100 end_log_pos 424 CRC32 0x82564e69 GTID last_committed=1 sequence_number=2 rbr_only=no #### 注意行4 SET @@SESSION.GTID_NEXT= 'a659234f-6aea-11e8-a361-000c29ed4cf4:59'/*!*/; #### 注意行5 # at 424 #180610 2:09:36 server id 100 end_log_pos 524 CRC32 0xbc21683a Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1528567776/*!*/; create table t2(n int) /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; #### 注意行6 DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;其中：“注意行1”中Previous-GTIDs代表的gtid集合是曾经的gtid，换句话说是被purge掉的事务。“注意行2”和”注意行4”是两个事务的gtid信息。它们写在每个事务的前面。“注意行3”和”注意行5”设置了GTID_NEXT的值，表示读取到了该事务后，那么必须要执行的是稍后列出的这个事务。“注意行6”是在所有事务执行结束时设置的，表示自动获取gtid的值。它对复制是隐身的(也就是说不会dump线程不会将它dump出去)，该行的结尾也说了，这一行是mysqlbinlog添加的。6.3 一些重要的变量gtid_mode：是否开启gtid复制模式。只允许on/off类的布尔值，不允许其他类型(如1/0)的布尔值，实际上这个变量是枚举类型的。要设置，必须同时设置开。在MySQL 5.6中，还必须开启，即使是master也要开启。gtid_mode：是否开启gtid复制模式。只允许on/off类的布尔值，不允许其他类型(如1/0)的布尔值，实际上这个变量是枚举类型的。要设置 ，必须同时设置 开。在MySQL 5.6中，还必须开启 ，即使是master也要开启。enforce_gtid_consistency：强制要求只允许复制事务安全的事务。gtid_mode=on时必须显式设置该项，如果不给定值，则默认为on。应该尽量将该选项放在gtid_mode的前面，减少启动mysqld时的检查。不能在事务内部创建和删除临时表。只能在事务外部进行，且autocommit需要设置为1。不能执行语句。该语句除了创建一张新表并填充一些数据，其他什么事也没干。不能在事务内既更新事务表又更新非事务表。gtid_executed：已经执行过的GTID。会清空该项的全局变量值。gtid_purged：已经purge掉的gtid。要设置该项，必须先保证已经为空，这意味着也一定会同时设置该项为空。在slave上设置该项时，表示稍后启动io线程和SQL线程都跳过这些gtid，slave上设置时应该让此项的gtid集合等于master上的值。gtid_next：表示下一个要执行的gtid事务。不能在事务内部创建和删除临时表。只能在事务外部进行，且autocommit需要设置为1。不能执行 语句。该语句除了创建一张新表并填充一些数据，其他什么事也没干。不能在事务内既更新事务表又更新非事务表。gtid_executed：已经执行过的GTID。 会清空该项的全局变量值。gtid_purged：已经purge掉的gtid。要设置该项，必须先保证 已经为空，这意味着也一定会同时设置该项为空。在slave上设置该项时，表示稍后启动io线程和SQL线程都跳过这些gtid，slave上设置时应该让此项的gtid集合等于master上 的值。gtid_next：表示下一个要执行的gtid事务。需要注意，master和slave上都有gtid_executed和gtid_purged，它们代表的意义有时候是不同的。还有一些变量，可能用到的不会多。如有需要，可翻官方手册。6.4 mysql.gtid_executed表MySQL 5.7中添加了一张记录已执行gtid的表mysql.gtid_executed，所以slave上的binlog不是必须开启的。mysql> select * from mysql.gtid_executed; +--------------------------------------+----------------+--------------+ | source_uuid | interval_start | interval_end | +--------------------------------------+----------------+--------------+ | a659234f-6aea-11e8-a361-000c29ed4cf4 | 1 | 57 | | a659234f-6aea-11e8-a361-000c29ed4cf4 | 58 | 58 | | a659234f-6aea-11e8-a361-000c29ed4cf4 | 59 | 59 | +--------------------------------------+----------------+--------------+7.一张图说明GTID复制在前面第6节中，使用了xtrabackup备份的方式提供gtid复制的基准数据。其中涉及到一些gtid检查、设置的操作。通过这些操作，大概可以感受的到gtid复制的几个概念。用一张图来说明：假如当前master的gtid为A3，已经purge掉的gtid为”1–>A1”，备份到slave上的数据为1-A2部分。如果A1 = 0，表示master的binlog没有被Purge过。slave可以直接开启gtid复制，但这样可能速度较慢，因为slave要复制所有binlog。也可以将master数据备份到slave上，然后设置 跳过备份结束时的gtid，这样速度较快。如果A1 != 0，表示master上的binlog中删除了一部分gtid。此时slave上必须先从master处恢复purge掉的那部分日志对应的数据。上图中备份结束时的GTID为A2。然后slave开启复制，唯一需要考虑的是”是否需要设置 跳过一部分gtid以避免重复执行”。备份数据到slave上，方式可以是mysqldump、冷备份、xtrabackup备份都行。由于gtid复制的特性，所需要的操作都很少，也很简单，前提是理解了”gtid的生命周期”。
OLTP和OLAP对比哪个更好用？二者有什么区别？
OLTP概念操作型处理，叫联机事务处理OLTP(On-LineTransactionProcessing)，主要目标是做数据处理，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性和并发支持的用户数等问题。传统的关系型数据库系统(RDBMS)作为数据管理的主要手段，主要用于操作型处理。OLAP概念分析型处理，叫联机分析处理OLAP(On-Line Analytical Processing)，主要目标是做数据分析。一般针对某些主题的历史数据进行复杂的多维分析，支持管理决策。数据仓库是OLAP系统的一个典型示例，主要用于数据分析。OLTP、OLAP对比OLTP是联机事务处理，OLAP是联机分析处理，相较来说还是有很多大差别的，具体可以参照下表：OLAP引擎分类OLAP按存储器的数据存储格式分为MOLAP(Multi-dimensional OLAP) 、ROLAP(Relational OLAP)和 HOLAP(Hybrid OLAP)。MOLAP，基于多维数组的存储模型，也是OLAP最初的形态，特点是对数据进行预计算，以空间换效率，明细和聚合数据都保存在cube中。但生成cube需要大量时间和空间。MOLAP可选Kylin、Druid等开源产品。ROLAP，完全基于关系模型进行存储数据，不需要预计算，按需即时查询。明细和汇总数据都保存在关系型数据库事实表中。HOLAP，混合模型，细节数据以ROLAP存放，聚合数据以MOLAP存放。这种方式相对灵活，且更加高效。开源的OLAP引擎有以下几类，可以看到Doris拥有众多优点，运维成本低，兼容Mysql访问协议等等，在变化维、非预设维、细粒度统计的应用场景下，使用MPP引擎驱动的ROLAP模式，可以简化模型设计，减少预计算的代价，并通过强大的实时计算能力，可以支撑良好的实时交互体验。doris是一个ROLAP引擎, 可以满足：灵活多维分析、明细+聚合、主键更新。